事务 PostgreSQL Oracle 数据库开发 MySQL SQL Server SQL

合并数据库中的数据

<b>Dipon<font color="orange">Roy</font></b>

4.20/5 (3投票s)

2022 年 6 月 29 日

CPOL

3分钟阅读

7290

122

如何在不同数据库中使用 MERGE 语句

下载源代码 - 5.8 KB

引言

MERGE 语句是一个非常流行的子句，可以在单个事务中管理 insert、update 和 delete。在本文中，我们将检查如何在不同的数据库中使用这些 MERGE 语句。我们将检查其他替代方法，因为并非所有数据库和版本都支持 MERGE 语句。重点是探索语法/使用差异，同时考虑不同的数据库，并提供最少的解释。

背景

假设我们有两个名为 source 和 target 的表，我们需要根据从 source 表中匹配的值来更新 target 表。

现在的情况是

source 表有一些行在 target 表中不存在。在这种情况下，我们需要将 source 表中的行添加到 target 表中。
source 表有一些行与 target 表中的行具有相同的键。但是，这些行在其他列中的值不同。在这种情况下，我们需要使用来自 source 表中的值来更新 target 表中的这些行。
target 表有一些行在 source 表中不存在。在这种情况下，我们需要从 target 表中删除这些行。

我们需要什么？

唯一标识符/用于标识每一行的逻辑
- 主键
- 复合键
- 唯一列或列的组合
数据更改指示器以检测数据更改
- 行版本
- 上次修改或创建的日期时间指示器
- 唯一值数据列

在大多数情况下，我们可能不需要考虑任何数据更改。所以一个唯一的/任何标识符/逻辑就足够了。

当前示例

在当前场景中，我们有一个 Id 列，它是唯一标识每一行的主键，以及一个 UpdatedDateTime 列来跟踪新的数据更改。

SQL Server

表和数据

让我们创建表并插入数据

CREATE TABLE TblUser(
    Id INT PRIMARY KEY,
    Email NVARCHAR(100),
    CreatedDateTime DATETIME NOT NULL,
    UpdatedDateTime DATETIME NULL,    
    SyncCreatedDateTime DATETIME NOT NULL,
    SyncUpdatedDateTime DATETIME NULL
);
CREATE TABLE TblEmployee(
    Id INT PRIMARY KEY,
    Email NVARCHAR(100),
    CreatedDateTime DATETIME NOT NULL,
    UpdatedDateTime DATETIME NULL
);

INSERT 
INTO TblUser (Id, Email, CreatedDateTime, UpdatedDateTime, SyncCreatedDateTime)
VALUES
(1001, 'Kong@hotmail.com', CONVERT(DATETIME, '2021-08-15', 102), _
        NULL , GETDATE()), --should be deleted
(1000, 'Han@hotmail.com', CONVERT(DATETIME, '2021-08-15', 102), _
        NULL , GETDATE()),  --should be deleted
(1, 'Dan@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), _
     NULL , GETDATE()),       --should be as it is
(2, 'Ben@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), _
     NULL , GETDATE()),       --should be as it is
(3, 'Danx@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), _
     CONVERT(DATETIME, '2021-08-16', 102), GETDATE()),        --should be as it is
(4, 'Benx@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), _
     CONVERT(DATETIME, '2021-08-16', 102), GETDATE()),        --should be as it is
(5, 'Jhon@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), _
     NULL , GETDATE()), --should be modified
(6, 'ken@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), _
     NULL , GETDATE()),  --should be modified
(7, 'Aron@facebook.com', CONVERT(DATETIME, '2021-08-15', 102), _
     CONVERT(DATETIME, '2021-08-16', 102) , GETDATE()),       --should be modified
(8, 'Kim@facebook.com', CONVERT(DATETIME, '2021-08-15', 102), _
     CONVERT(DATETIME, '2021-08-16', 102) , GETDATE());       --should be modified
--(9, 'Tom@yahoo.com', DATEADD(DD,1,GETDATE()), NULL , GETDATE())    --should be added
--(10, 'Jeff@yahoo.com',DATEADD(DD,1,GETDATE()), NULL , GETDATE())   --should be added

INSERT 
INTO TblEmployee (Id, Email, CreatedDateTime, UpdatedDateTime)
VALUES
(1, 'Dan-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), NULL),
(2, 'Ben-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), NULL),
(3, 'Danx-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), _
     CONVERT(DATETIME, '2021-08-16', 102)),
(4, 'Benx-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), _
     CONVERT(DATETIME, '2021-08-16', 102)),
(5, 'Jhon@outlook.com', CONVERT(DATETIME, '2021-08-15', 102), DATEADD(DD,1,GETDATE())),
(6, 'ken@outlook.com', CONVERT(DATETIME, '2021-08-15', 102), DATEADD(DD,1,GETDATE())),
(7, 'Aron@mail.com', CONVERT(DATETIME, '2021-08-15', 102), _
     CONVERT(DATETIME, '2021-08-20', 102)),
(8, 'Kim@mail.com', CONVERT(DATETIME, '2021-08-15', 102), _
     CONVERT(DATETIME, '2021-08-20', 102)),
(9, 'Tom@yahoo.com', CONVERT(DATETIME, '2021-08-16', 102), NULL),
(10, 'Jeff@yahoo.com', CONVERT(DATETIME, '2021-08-16', 102), NULL);

使用 MERGE 语句

SQL Server 的 merge 语句非常简单。

MERGE TblUser AS T
USING TblEmployee AS S
ON T.Id = S.Id
WHEN MATCHED AND COALESCE(S.UpdatedDateTime, S.CreatedDateTime, _
     GETDATE()) <> COALESCE(T.UpdatedDateTime, T.CreatedDateTime, GETDATE())
    THEN UPDATE 
        SET 
            T.Email = S.Email,
            T.UpdatedDateTime = S.UpdatedDateTime,
            T.SyncUpdatedDateTime = GETDATE()
WHEN NOT MATCHED BY TARGET
    THEN INSERT (Id, Email, CreatedDateTime, SyncCreatedDateTime)
        VALUES (S.Id, S.Email, S.CreatedDateTime, GETDATE())
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

source 表不一定是实际的表，我们也可以使用如下的内联数据

MERGE TblUser AS T
USING ( VALUES
(1, 'Dan-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), NULL),
(2, 'Ben-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), NULL),
(3, 'Danx-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), _
     CONVERT(DATETIME, '2021-08-16', 102)),
(4, 'Benx-x@gmail.com', CONVERT(DATETIME, '2021-08-15', 102), _
     CONVERT(DATETIME, '2021-08-16', 102)),
(5, 'Jhon@outlook.com', CONVERT(DATETIME, '2021-08-15', 102), DATEADD(DD,1,GETDATE())),
(6, 'ken@outlook.com', CONVERT(DATETIME, '2021-08-15', 102), DATEADD(DD,1,GETDATE())),
(7, 'Aron@mail.com', CONVERT(DATETIME, '2021-08-15', 102), _
     CONVERT(DATETIME, '2021-08-20', 102)),
(8, 'Kim@mail.com', CONVERT(DATETIME, '2021-08-15', 102), _
     CONVERT(DATETIME, '2021-08-20', 102)),
(9, 'Tom@yahoo.com', CONVERT(DATETIME, '2021-08-16', 102), NULL),
(10, 'Jeff@yahoo.com', CONVERT(DATETIME, '2021-08-16', 102), NULL)
) AS S(Id, Email, CreatedDateTime, UpdatedDateTime)
ON T.Id = S.Id
WHEN MATCHED AND COALESCE(S.UpdatedDateTime, S.CreatedDateTime, GETDATE()) _
     <> COALESCE(T.UpdatedDateTime, T.CreatedDateTime, GETDATE())
    THEN UPDATE 
        SET 
            T.Email = S.Email,
            T.UpdatedDateTime = S.UpdatedDateTime,
            T.SyncUpdatedDateTime = GETDATE()
WHEN NOT MATCHED BY TARGET
    THEN INSERT (Id, Email, CreatedDateTime, SyncCreatedDateTime)
        VALUES (S.Id, S.Email, S.CreatedDateTime, GETDATE())
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

使用常规 INSERT UPDATE DELETE 语句

删除行部分将从用户表中删除不必要的行
更新行部分将使用更新后的数据更新用户表的行
添加行将向用户表中添加新行

-------------------------------- delete rows
--SELECT * FROM TblUser
DELETE FROM TblUser
WHERE NOT EXISTS (
    SELECT E.Id    
    FROM TblEmployee E    
    WHERE  E.Id = TblUser.Id
);

-------------------------------- update rows
WITH ExistingUsers
AS
(
    SELECT *
    FROM TblEmployee E
    WHERE EXISTS (
        SELECT Id    
        FROM TblUser U
        WHERE  E.Id = U.Id
        AND COALESCE(E.UpdatedDateTime, E.CreatedDateTime, _
        GETDATE()) <> COALESCE(U.UpdatedDateTime, U.CreatedDateTime, GETDATE())
    )
)
UPDATE U
SET 
    U.Email = E.Email,
    U.UpdatedDateTime = E.UpdatedDateTime,
    U.SyncUpdatedDateTime = GETDATE()
FROM  TblUser U
JOIN ExistingUsers E ON U.Id = E.Id;

------------------------------ add rows
INSERT INTO TblUser (Id, Email, CreatedDateTime, SyncCreatedDateTime)
SELECT Id, Email, CreatedDateTime, GETDATE()
FROM TblEmployee E
WHERE NOT EXISTS (
    SELECT Id    
    FROM TblUser U
    WHERE  E.Id = U.Id
);

ORACLE

表和数据

CREATE TABLE TblUser(
    Id INT,
    Email VARCHAR(100),
    CreatedDateTime TIMESTAMP NOT NULL,
    UpdatedDateTime TIMESTAMP NULL,    
    SyncCreatedDateTime TIMESTAMP NOT NULL,
    SyncUpdatedDateTime TIMESTAMP NULL,
  
    PRIMARY KEY(Id)
);
CREATE TABLE TblEmployee(
    Id INT,
    Email VARCHAR(100),
    CreatedDateTime TIMESTAMP NOT NULL,
    UpdatedDateTime TIMESTAMP NULL,
  
    PRIMARY KEY(Id)
);

INSERT INTO TblUser (Id, Email, CreatedDateTime, UpdatedDateTime, SyncCreatedDateTime)
    SELECT 1001, 'Kong@hotmail.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
           NULL, CURRENT_DATE FROM DUAL                       --should be deleted
    UNION ALL 
    SELECT 1000, 'Han@hotmail.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
           NULL, CURRENT_DATE FROM DUAL                       --should be deleted
    UNION ALL 
    SELECT 1, 'Dan@gmail.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
           NULL, CURRENT_DATE FROM DUAL                       --should be as it is
    UNION ALL 
    SELECT 2, 'Ben@gmail.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
           NULL, CURRENT_DATE FROM DUAL                       --should be as it is
    UNION ALL 
    SELECT 3, 'Danx@gmail.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
           TO_TIMESTAMP('2021-08-16', 'yyyy-mm-dd'), _
           CURRENT_DATE FROM DUAL                             --should be as it is
    UNION ALL 
    SELECT 4, 'Benx@gmail.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
           TO_TIMESTAMP('2021-08-16', 'yyyy-mm-dd'), _
           CURRENT_DATE FROM DUAL                             --should be as it is
    UNION ALL 
    SELECT 5, 'Jhon@gmail.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
           NULL, CURRENT_DATE FROM DUAL                       --should be modified
    UNION ALL 
    SELECT 6, 'ken@gmail.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
           NULL, CURRENT_DATE FROM DUAL                       --should be modified
    UNION ALL 
    SELECT 7, 'Aron@facebook.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
           TO_TIMESTAMP('2021-08-16', 'yyyy-mm-dd'), _
           CURRENT_DATE FROM DUAL                             --should be modified
    UNION ALL 
    SELECT 8, 'Kim@facebook.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
           TO_TIMESTAMP('2021-08-16', 'yyyy-mm-dd'), _
           CURRENT_DATE FROM DUAL    /*should be modified*/
    ;    
    --(9, 'Tom@yahoo.com', CURRENT_DATE + INTERVAL '1' DAY, NULL, CURRENT_DATE)   
    --should be added
    --(10, 'Jeff@yahoo.com',CURRENT_DATE + INTERVAL '1' DAY, NULL, CURRENT_DATE)  
    --should be added

INSERT INTO TblEmployee (Id, Email, CreatedDateTime, UpdatedDateTime)
    WITH List AS (
        SELECT 1, 'Dan-x@gmail.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
                   NULL FROM DUAL
        UNION
        SELECT 2, 'Ben-x@gmail.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
                   NULL FROM DUAL
        UNION
        SELECT 3, 'Danx-x@gmail.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
                   TO_TIMESTAMP('2021-08-16', 'yyyy-mm-dd') FROM DUAL
        UNION
        SELECT 4, 'Benx-x@gmail.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
                   TO_TIMESTAMP('2021-08-16', 'yyyy-mm-dd') FROM DUAL
        UNION
        SELECT 5, 'Jhon@outlook.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
                   CURRENT_DATE + INTERVAL '1' DAY FROM DUAL
        UNION
        SELECT 6, 'ken@outlook.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
                   CURRENT_DATE + INTERVAL '1' DAY FROM DUAL
        UNION
        SELECT 7, 'Aron@mail.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
                   TO_TIMESTAMP('2021-08-20', 'yyyy-mm-dd') FROM DUAL
        UNION
        SELECT 8, 'Kim@mail.com', TO_TIMESTAMP('2021-08-15', 'yyyy-mm-dd'), _
                   TO_TIMESTAMP('2021-08-20', 'yyyy-mm-dd') FROM DUAL
        UNION
        SELECT 9, 'Tom@yahoo.com', TO_TIMESTAMP('2021-08-16', 'yyyy-mm-dd'), _
                   NULL FROM DUAL
        UNION
        SELECT 10, 'Jeff@yahoo.com', TO_TIMESTAMP('2021-08-16', 'yyyy-mm-dd'), _
                    NULL FROM DUAL
    )
    SELECT * FROM List;

使用 MERGE 语句

此 merge 语句与 SQL Server 的语句略有不同。

MERGE INTO TblUser U
USING (
    SELECT Id , Email, CreatedDateTime, UpdatedDateTime, 0 ShouldBeDeleted
    FROM TblEmployee
    UNION ALL
    /*these rows will be deleted*/
    SELECT Id , Email, CreatedDateTime, UpdatedDateTime, 1 ShouldBeDeleted
    FROM TblUser EU
    WHERE NOT EXISTS (
        SELECT Id
        FROM TblEmployee
        WHERE Id = EU.Id
    )
) E
ON (U.Id = E.Id)
WHEN MATCHED
    THEN UPDATE
      SET 
          U.Email = E.Email,
          U.UpdatedDateTime = E.UpdatedDateTime,
          U.SyncUpdatedDateTime = CURRENT_DATE
      WHERE (COALESCE(E.UpdatedDateTime, E.CreatedDateTime, _
      CURRENT_DATE) <> COALESCE(U.UpdatedDateTime, U.CreatedDateTime, CURRENT_DATE))
        OR E.ShouldBeDeleted = 1 /*without updating Oracle will not delete the rows*/

        DELETE WHERE E.ShouldBeDeleted = 1    
        
WHEN NOT MATCHED 
    THEN INSERT (Id, Email, CreatedDateTime, SyncCreatedDateTime)
        VALUES (E.Id, E.Email, E.CreatedDateTime, CURRENT_DATE);

DELETE 的内容实际上是 MATCHED 和 UPDATE 部分的一部分。所以要先 DELETE 某些内容，我们需要获取匹配的行，并且在更新该行之后，我们必须决定是否立即删除它或让它保持原样。

使用常规 INSERT UPDATE DELETE 语句

-------------------------------- delete rows
--SELECT * FROM TblUser
DELETE FROM TblUser
WHERE NOT EXISTS (
    SELECT E.Id    
    FROM TblEmployee E    
    WHERE  E.Id = TblUser.Id
);

-------------------------------- update rows
UPDATE TblUser U
SET (
        U.Email,
        U.UpdatedDateTime,
        U.SyncUpdatedDateTime
    ) = (
        SELECT
            E.Email,
            E.UpdatedDateTime,
            CURRENT_DATE
        FROM TblEmployee E
        WHERE E.Id = U.Id
    )
WHERE EXISTS (
    SELECT E.Id    
    FROM TblEmployee E
    WHERE E.Id = U.Id
    AND COALESCE(E.UpdatedDateTime, E.CreatedDateTime, _
        CURRENT_DATE) <> COALESCE(U.UpdatedDateTime, U.CreatedDateTime, CURRENT_DATE)
);

----------------------------- add rows
INSERT INTO TblUser (Id, Email, CreatedDateTime, SyncCreatedDateTime)
SELECT Id, Email, CreatedDateTime, CURRENT_DATE
FROM TblEmployee E
WHERE NOT EXISTS (
    SELECT Id    
    FROM TblUser U
    WHERE  E.Id = U.Id
);

MySQL

表和数据

CREATE TABLE TblUser(
    Id INT PRIMARY KEY,
    Email NVARCHAR(100),
    CreatedDateTime DATETIME NOT NULL,
    UpdatedDateTime DATETIME NULL,    
    SyncCreatedDateTime DATETIME NOT NULL,
    SyncUpdatedDateTime DATETIME NULL
);
CREATE TABLE TblEmployee(
    Id INT PRIMARY KEY,
    Email NVARCHAR(100),
    CreatedDateTime DATETIME NOT NULL,
    UpdatedDateTime DATETIME NULL
);

INSERT 
INTO TblUser (Id, Email, CreatedDateTime, UpdatedDateTime, SyncCreatedDateTime)
VALUES
(1001, 'Kong@hotmail.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
        NULL , NOW()), #should be deleted
(1000, 'Han@hotmail.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
        NULL , NOW()),  #should be deleted
(1, 'Dan@gmail.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
     NULL , NOW()),       #should be as it is
(2, 'Ben@gmail.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
     NULL , NOW()),       #should be as it is
(3, 'Danx@gmail.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
     STR_TO_DATE('2021-08-16', '%Y-%m-%d'), NOW()),        #should be as it is
(4, 'Benx@gmail.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
     STR_TO_DATE('2021-08-16', '%Y-%m-%d'), NOW()),        #should be as it is
(5, 'Jhon@gmail.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
     NULL , NOW()),  #should be modified
(6, 'ken@gmail.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
     NULL , NOW()),   #should be modified
(7, 'Aron@facebook.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
     STR_TO_DATE('2021-08-16', '%Y-%m-%d') , NOW()),    #should be modified
(8, 'Kim@facebook.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
     STR_TO_DATE('2021-08-16', '%Y-%m-%d') , NOW());    #should be modified
#(9, 'Tom@yahoo.com', DATE_ADD(NOW(), INTERVAL 1 DAY), _
     NULL , NOW())       #should be added
#(10, 'Jeff@yahoo.com',DATE_ADD(NOW(), INTERVAL 1 DAY), _
       NULL , NOW())      #should be added

INSERT 
INTO TblEmployee (Id, Email, CreatedDateTime, UpdatedDateTime)
VALUES
(1, 'Dan-x@gmail.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), NULL),
(2, 'Ben-x@gmail.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), NULL),
(3, 'Danx-x@gmail.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
     STR_TO_DATE('2021-08-16', '%Y-%m-%d')),
(4, 'Benx-x@gmail.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
     STR_TO_DATE('2021-08-16', '%Y-%m-%d')),
(5, 'Jhon@outlook.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
     DATE_ADD(NOW(), INTERVAL 1 DAY)),
(6, 'ken@outlook.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
     DATE_ADD(NOW(), INTERVAL 1 DAY)),
(7, 'Aron@mail.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
     STR_TO_DATE('2021-08-20', '%Y-%m-%d')),
(8, 'Kim@mail.com', STR_TO_DATE('2021-08-15', '%Y-%m-%d'), _
     STR_TO_DATE('2021-08-20', '%Y-%m-%d')),
(9, 'Tom@yahoo.com', STR_TO_DATE('2021-08-16', '%Y-%m-%d'), NULL),
(10, 'Jeff@yahoo.com', STR_TO_DATE('2021-08-16', '%Y-%m-%d'), NULL);

使用 MERGE 语句

没有 MERGE 语句可用。

使用常规 INSERT UPDATE DELETE 语句

################ delete rows
#SELECT * FROM TblUser
DELETE FROM TblUser
WHERE NOT EXISTS (
    SELECT E.Id    
    FROM TblEmployee E    
    WHERE  E.Id = TblUser.Id
);

################ update rows
#With query only works with 8.x version
#db version 5.5 5.6 or less
UPDATE TblUser U
JOIN (
    SELECT *
    FROM TblEmployee E
    WHERE EXISTS (
        SELECT Id    
        FROM TblUser U
        WHERE  E.Id = U.Id
        AND COALESCE(E.UpdatedDateTime, E.CreatedDateTime, _
        NOW()) <> COALESCE(U.UpdatedDateTime, U.CreatedDateTime, NOW())
    )
) EU ON U.Id = EU.Id
SET 
    U.Email = EU.Email,
    U.UpdatedDateTime = EU.UpdatedDateTime,
    U.SyncUpdatedDateTime = NOW();

################ add rows
INSERT INTO TblUser (Id, Email, CreatedDateTime, SyncCreatedDateTime)
SELECT Id, Email, CreatedDateTime, NOW()
FROM TblEmployee E
WHERE NOT EXISTS (
    SELECT Id    
    FROM TblUser U
    WHERE  E.Id = U.Id
);

在使用 MySQL 8 时，更新行查询未按预期工作。从版本 5.7 开始，SELECT FROM 和 UPDATE 同一个表不起作用。所以稍微更改一下查询。

################ update rows
#select from and update same table not working from db version 5.7, 8
CREATE TEMPORARY TABLE ExistingUser
SELECT *
FROM TblEmployee E
WHERE EXISTS (
    SELECT Id    
    FROM TblUser U
    WHERE  E.Id = U.Id
    AND COALESCE(E.UpdatedDateTime, E.CreatedDateTime, _
        NOW()) <> COALESCE(U.UpdatedDateTime, U.CreatedDateTime, NOW())
); 
               
UPDATE TblUser U
JOIN ExistingUser EU ON U.Id = EU.Id
SET 
    U.Email = EU.Email,
    U.UpdatedDateTime = EU.UpdatedDateTime,
    U.SyncUpdatedDateTime = NOW();
    
DROP TEMPORARY TABLE ExistingUser;

PostgreSQL

表和数据

CREATE TABLE TblUser(
    Id INT PRIMARY KEY,
    Email VARCHAR(100),
    CreatedDateTime TIMESTAMP NOT NULL,
    UpdatedDateTime TIMESTAMP NULL,    
    SyncCreatedDateTime TIMESTAMP NOT NULL,
    SyncUpdatedDateTime TIMESTAMP NULL
);
CREATE TABLE TblEmployee(
    Id INT PRIMARY KEY,
    Email VARCHAR(100),
    CreatedDateTime TIMESTAMP NOT NULL,
    UpdatedDateTime TIMESTAMP NULL
);

INSERT 
INTO TblUser (Id, Email, CreatedDateTime, UpdatedDateTime, SyncCreatedDateTime)
VALUES
(1001, 'Kong@hotmail.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), _
        NULL , NOW()),--should be deleted
(1000, 'Han@hotmail.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), _
        NULL , NOW()), --should be deleted
(1, 'Dan@gmail.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), _
     NULL , NOW()),      --should be as it is
(2, 'Ben@gmail.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), _
     NULL , NOW()),      --should be as it is
(3, 'Danx@gmail.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), _
     TO_DATE('2021-08-16', 'YYYY-MM-DD'), NOW()),        --should be as it is
(4, 'Benx@gmail.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), _
     TO_DATE('2021-08-16', 'YYYY-MM-DD'), NOW()),        --should be as it is
(5, 'Jhon@gmail.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), _
     NULL , NOW()),   --should be modified
(6, 'ken@gmail.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), _
     NULL , NOW()),    --should be modified
(7, 'Aron@facebook.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), _
     TO_DATE('2021-08-16', 'YYYY-MM-DD') , NOW()),    --should be modified
(8, 'Kim@facebook.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), _
     TO_DATE('2021-08-16', 'YYYY-MM-DD') , NOW());    --should be modified
--(9, 'Tom@yahoo.com', NOW() + INTERVAL '1 day', NULL , NOW())         --should be added
--(10, 'Jeff@yahoo.com',NOW() + INTERVAL '1 day', NULL , NOW())        --should be added

INSERT 
INTO TblEmployee (Id, Email, CreatedDateTime, UpdatedDateTime)
VALUES
(1, 'Dan-x@gmail.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), NULL),
(2, 'Ben-x@gmail.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), NULL),
(3, 'Danx-x@gmail.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), _
     TO_DATE('2021-08-16', 'YYYY-MM-DD')),
(4, 'Benx-x@gmail.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), _
     TO_DATE('2021-08-16', 'YYYY-MM-DD')),
(5, 'Jhon@outlook.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), NOW() + INTERVAL '1 day'),
(6, 'ken@outlook.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), NOW() + INTERVAL '1 day'),
(7, 'Aron@mail.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), _
     TO_DATE('2021-08-20', 'YYYY-MM-DD')),
(8, 'Kim@mail.com', TO_DATE('2021-08-15', 'YYYY-MM-DD'), _
     TO_DATE('2021-08-20', 'YYYY-MM-DD')),
(9, 'Tom@yahoo.com', TO_DATE('2021-08-16', 'YYYY-MM-DD'), NULL),
(10, 'Jeff@yahoo.com', TO_DATE('2021-08-16', 'YYYY-MM-DD'), NULL);

使用 MERGE 语句

没有 MERGE 语句可用。

使用常规 INSERT UPDATE DELETE 语句

-------------------------------- delete rows
--SELECT * FROM TblUser
DELETE FROM TblUser
WHERE NOT EXISTS (
    SELECT E.Id    
    FROM TblEmployee E    
    WHERE  E.Id = TblUser.Id
);

-------------------------------- update rows
WITH ExistingUsers
AS
(
    SELECT *
    FROM TblEmployee E
    WHERE EXISTS (
        SELECT Id    
        FROM TblUser U
        WHERE  E.Id = U.Id
        AND COALESCE(E.UpdatedDateTime, E.CreatedDateTime, _
            NOW()) <> COALESCE(U.UpdatedDateTime, U.CreatedDateTime, NOW())
    )
)
UPDATE TblUser U
SET 
    Email = E.Email,
    UpdatedDateTime = E.UpdatedDateTime,
    SyncUpdatedDateTime = NOW()
FROM  ExistingUsers E
WHERE U.Id = E.Id;

------------------------------ add rows
INSERT INTO TblUser (Id, Email, CreatedDateTime, SyncCreatedDateTime)
SELECT Id, Email, CreatedDateTime, NOW()
FROM TblEmployee E
WHERE NOT EXISTS (
    SELECT Id    
    FROM TblUser U
    WHERE  E.Id = U.Id
);

SQLite

表和数据

CREATE TABLE TblUser(
    Id INT PRIMARY KEY,
    Email NVARCHAR(100),
    CreatedDateTime DATETIME NOT NULL,
    UpdatedDateTime DATETIME NULL,    
    SyncCreatedDateTime DATETIME NOT NULL,
    SyncUpdatedDateTime DATETIME NULL
);
CREATE TABLE TblEmployee(
    Id INT PRIMARY KEY,
    Email NVARCHAR(100),
    CreatedDateTime DATETIME NOT NULL,
    UpdatedDateTime DATETIME NULL
);

INSERT 
INTO TblUser (Id, Email, CreatedDateTime, UpdatedDateTime, SyncCreatedDateTime)
VALUES
(1001, 'Kong@hotmail.com', STRFTIME('%Y/%m/%d', '2021-08-15'), _
        NULL , DATE()), --should be deleted
(1000, 'Han@hotmail.com', STRFTIME('%Y/%m/%d', '2021-08-15'), _
        NULL , DATE()),  --should be deleted
(1, 'Dan@gmail.com', STRFTIME('%Y/%m/%d', '2021-08-15'), _
     NULL , DATE()),       --should be as it is
(2, 'Ben@gmail.com', STRFTIME('%Y/%m/%d', '2021-08-15'), _
     NULL , DATE()),       --should be as it is
(3, 'Danx@gmail.com', STRFTIME('%Y/%m/%d', '2021-08-15'), _
     STRFTIME('%Y/%m/%d', '2021-08-16'), DATE()),        --should be as it is
(4, 'Benx@gmail.com', STRFTIME('%Y/%m/%d', '2021-08-15'), _
     STRFTIME('%Y/%m/%d', '2021-08-16'), DATE()),        --should be as it is
(5, 'Jhon@gmail.com', STRFTIME('%Y/%m/%d', '2021-08-15'), _
     NULL , DATE()), --should be modified
(6, 'ken@gmail.com', STRFTIME('%Y/%m/%d', '2021-08-15'), _
     NULL , DATE()),  --should be modified
(7, 'Aron@facebook.com', STRFTIME('%Y/%m/%d', '2021-08-15'), _
     STRFTIME('%Y/%m/%d', '2021-08-16') , DATE()),    --should be modified
(8, 'Kim@facebook.com', STRFTIME('%Y/%m/%d', '2021-08-15'), _
     STRFTIME('%Y/%m/%d', '2021-08-16') , DATE())     /*should be modified*/
;
--(9, 'Tom@yahoo.com', DATE(DATE(), '+1 day'), NULL , DATE()) should be added
--(10, 'Jeff@yahoo.com',DATE(DATE(), '+1 day'), NULL , DATE()) should be added

INSERT 
INTO TblEmployee (Id, Email, CreatedDateTime, UpdatedDateTime)
VALUES
(1, 'Dan-x@gmail.com', STRFTIME('%Y/%m/%d', '2021-08-15'), NULL),
(2, 'Ben-x@gmail.com', STRFTIME('%Y/%m/%d', '2021-08-15'), NULL),
(3, 'Danx-x@gmail.com', STRFTIME('%Y/%m/%d', '2021-08-15'), _
     STRFTIME('%Y/%m/%d', '2021-08-16')),
(4, 'Benx-x@gmail.com', STRFTIME('%Y/%m/%d', '2021-08-15'), _
     STRFTIME('%Y/%m/%d', '2021-08-16')),
(5, 'Jhon@outlook.com', STRFTIME('%Y/%m/%d', '2021-08-15'), DATE(DATE(), '+1 day')),
(6, 'ken@outlook.com', STRFTIME('%Y/%m/%d', '2021-08-15'), DATE(DATE(), '+1 day')),
(7, 'Aron@mail.com', STRFTIME('%Y/%m/%d', '2021-08-15'), _
     STRFTIME('%Y/%m/%d', '2021-08-20')),
(8, 'Kim@mail.com', STRFTIME('%Y/%m/%d', '2021-08-15'), _
     STRFTIME('%Y/%m/%d', '2021-08-20')),
(9, 'Tom@yahoo.com', STRFTIME('%Y/%m/%d', '2021-08-16'), NULL),
(10, 'Jeff@yahoo.com', STRFTIME('%Y/%m/%d', '2021-08-16'), NULL)

使用 MERGE 语句

没有 MERGE 语句可用。

使用常规 INSERT UPDATE DELETE 语句

-------------------------------- delete rows
--SELECT * FROM TblUser
DELETE FROM TblUser
WHERE NOT EXISTS (
    SELECT E.Id    
    FROM TblEmployee E    
    WHERE  E.Id = TblUser.Id
);

-------------------------------- update rows
UPDATE TblUser
SET 
    Email = E.Email,
    UpdatedDateTime = E.UpdatedDateTime,
    SyncUpdatedDateTime = DATE()
FROM (
    SELECT *
    FROM TblEmployee E
    WHERE EXISTS (
        SELECT Id    
        FROM TblUser U
        WHERE  E.Id = U.Id
        AND COALESCE(E.UpdatedDateTime, E.CreatedDateTime, _
        DATE()) <> COALESCE(U.UpdatedDateTime, U.CreatedDateTime, DATE())
    )
) E
WHERE TblUser.Id = E.Id;

------------------------------ add rows
INSERT INTO TblUser (Id, Email, CreatedDateTime, SyncCreatedDateTime)
SELECT Id, Email, CreatedDateTime, DATE()
FROM TblEmployee E
WHERE NOT EXISTS (
    SELECT Id    
    FROM TblUser U
    WHERE  E.Id = U.Id
);

其他帮助程序查询

以下是一些其他帮助程序查询。

DROP TABLE IF EXISTS TblUser;
DROP TABLE IF EXISTS TblEmployee;

DROP TABLE TblUser;
DROP TABLE TblEmployee;

TRUNCATE TABLE TblUser;
TRUNCATE TABLE TblEmployee;

SELECT * FROM TblUser;
SELECT * FROM TblEmployee;

以下查询只能在 SQL Server 数据库中使用。

/*only SQL Server*/
IF OBJECT_ID('dbo.TblUser') IS NOT NULL
    DROP TABLE TblUser;
IF OBJECT_ID('dbo.TblEmployee') IS NOT NULL
    DROP TABLE TblEmployee;

重要事项

MERGE 语句在一个 TRANSACTION 中管理插入、更新和删除
在使用常规 INSERT UPDATE DELETE 语句时，应该使用 TRANSACTION 语句

SQL Server 数据库的 TRANSACTION 语句示例

DECLARE @mainTran VARCHAR = 'TranName';
BEGIN TRANSACTION @mainTran;
BEGIN TRY
    /*delete existing rows*/
    /*update existing rows*/
    /*add new rows*/
    COMMIT TRANSACTION @mainTran
END TRY
BEGIN CATCH
    DECLARE @error VARCHAR = 'Some error message';
    ROLLBACK TRANSACTION @mainTran;
    THROW 50000, @error, 1;  
END CATCH

限制

具体内容可能会因数据库版本而异。

我的工作数据库版本是

Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Oracle Database 11g 企业版发布版 11.2.0.4.0 - 64 位产品
MySQL 5.5.61
PostgreSQL 10.5，编译自 Visual C++ build 1800, 64 位

历史

2022 年 6 月 29 日：初始版本