博客

合并数据库中的数据

  • Post author:
  • Post category:其他



目录


介绍


背景


我们需要什么？


当前示例


SQL Server


表和数据


使用MERGE语句


使用常规插入更新删除语句


ORACLE


表和数据


使用MERGE语句


使用常规插入更新删除语句


MySQL


表和数据


使用MERGE语句


使用常规插入更新删除语句


PostgreSQL


表和数据


使用MERGE语句


使用常规插入更新删除语句


SQLite


表和数据


使用MERGE语句


使用常规插入更新删除语句


其他帮助程序查询


重要事项


局限性



介绍





MERGE



语句是一个非常流行的子句，可以在单个事务中管理


insert


，


update


和


delete


。在这里，在本文中，我们将检查如何在不同的数据库中使用这些



MERGE



语句。我们将检查其他替代方法，因为所有数据库和版本都不支持该



MERGE



语句。重点是探索考虑不同数据库的语法


/


使用差异，并提供最少的解释。



背景




假设我们有两个表调用


source


和


target


我们需要根据与


source


表匹配的值更新


target


表。



现在的案例是：




  1. source

    表包含一些

    target

    表中不存在的行。在这个例子中，我们需要将

    source

    表中的行添加到

    target

    表中。



  2. source

    表有一些行与

    target

    表中的行具有相同的键。但是，这些行在其他列中具有不同的值。在这个例子中，我们需要使用来自

    source

    表的值在

    target

    表中更新它们。



  3. 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;



使用常规插入更新删除语句





  • “


    删除行


    ”

    部分将从用户表中删除不必要的行



  • “


    更新行


    ”

    部分将使用更新的数据更新用户表的行



  • “


    添加行


    ”

    将向用户表添加新行

     

-------------------------------- 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


语句




此语句与


SQL Server


的


merge


语句略有不同。

 

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


一些东西，我们需要获取匹配的行，在更新行后，我们必须决定是现在删除它还是让它保持原样。



使用常规插入更新删除语句


-------------------------------- 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


声明可用。



使用常规插入更新删除语句


################ 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


声明可用。



使用常规插入更新删除语句


-------------------------------- 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


声明可用。



使用常规插入更新删除语句


-------------------------------- 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


    语句中管理插入、更新和删除


  • 应该使用


    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



局限性




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



我的工作数据库版本是：



  • 微软SQL Server 2014 – 12.0.2000.8（X64）


  • Oracle数据库11g企业版版本11.2.0.4.0 – 64位生产


  • MySQL 5.5.61


  • PostgreSQL 10.5，由Visual C++编译，build 1800，64位


https://www.codeproject.com/Articles/5314891/Merge-Data-in-Database