合并数据库中的数据

最新推荐文章于 2023-11-30 17:06:29 发布
最新推荐文章于 2023-11-30 17:06:29 发布
阅读量1.5k 收藏 2
点赞数
分类专栏： 数据库 文章标签： 数据库 sql mysql
原文链接：https://www.codeproject.com/Articles/5314891/Merge-Data-in-Database
版权

目录

介绍

背景

我们需要什么？

当前示例

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

寒冰屋
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
arcpy合并数据库
07-07
本工具是在做生态保护红线时根据需求写的一个批量合并数据库的工具，可以批量把数据库追加到一个模板库内。仅限数据库是数据集，数据集下有要素的这种情况。如果数据库下没有数据集，全是要素，建议修改一下代码。...
数据库合并列数据
07-29
数据库合并列数据,就是一列下面的多个单元格合并为一个单元格
SQL Server多个数据库合并
SAP阿狸
05-13 1万+
1、创建两个数据库A_TEST，B_TEST。 2、将数据库A合并到数据库B。选中数据库，右键——任务——导出数据。 3、转到导出向导，选择下一步。 4、输入服务器名称，选择身份验证（默认Windows），选择导出的数据库名。然后“下一步”。 5、输入目标服务器名称（不跨服务器：服务器名称和上一步导出时的一致；跨服务器：需要输入服务名称），选择要合并的目标数据库B_TEST...
如何将几个相同的数据库合并为一个
XZZ_2018的博客
01-26 4527
大家好，才是真的好。 每年的北美时间12月7号是Notes诞生（1984年12月7号）37年的日子，也是正式的Notes产品发布（1989年12月7号）32年的日子。在此我们需要特别祝贺一下：Notes生日快乐！ 今天我们讲一下一个很简单的技术。 有时候一个小小的需求常常困扰着我们，比如将几个有数据的Notes应用合并为一个？ 这种需求很常出现，比如你自己创建了几个Notes笔记应用 想要在里面进行搜索数据，发现只能一个一个打开进行搜索。 又或者你是一个Domino服务器管理员，常常要在多个邮件库中查.
MERGE用法解析
huahualaly的博客
12-10 2210
MERGE用法解析 概念 MERGE提供了在多个表这件合并数据的能力，使用该语句可以有条件地更新和插入数据到数据库表中，在对数据进行插入时，如果行存在，则执行UPDATE语句进行更新；如果是一个新的行，则执行INSERT语句插。 由于MERGE命令组合了INSERT和UPDAET命令，因而需要有对目标表的INSERT和UPDATE权限，以及对源表的SELECT权限。MERGE语句语法如下： MERGE INTO table_name A USING table_name1 B on(JOIN condi
数据库批量合并 ：合并数据(shp,gdb)
wytp_vanity的博客
07-21 783
数据库批量合并 ：合并数据(shp,gdb)
合并多个ACCESS数据库数据(.mdb)的方法
xingshanchang的博客
04-21 4358
选择需要合并的表，支持 “全选 和 自行选择”【选项(O)>>】可以设置更详细的参数。
易语言Access数据库中的数据导出到Excel中.rar
02-23
易语言Access数据库中的数据导出到Excel中.rar 易语言Access数据库中的数据导出到Excel中.rar 易语言Access数据库中的数据导出到Excel中.rar 易语言Access数据库中的数据导出到Excel中.rar 易语言Access数据库中...
oracle异库同表名的两个数据库中数据合并或数据表结构的修改
03-01
oracle异库同表名的两个数据库中数据合并或数据表结构的修改
免费下载 FME案例实战教程：mdb个人地理数据库批量合并，解决FME中数据合并类功能需求
01-26
1.视频讲解MDB个人地理数据库批量合并案例，解决FME中数据合并类功能需求。 2.本视频讲解中使用的FME版本为2020.2中文版。 3.本资源仅为视频讲解，用户需自行安装相应版本的FME。
数据库合并
10-30
sql2000的数据库合并工具,可以将两个或以上的数据库中的数据合并到同一个数据库中
【ArcGIS Pro二次开发】(78)：批量合并GDB数据库
最新发布
规划GIS会
11-30 2293
获取GDB数据库中的所有要素数据集和所有要素，根据其原来的数据路径，在目标GDB数据库中，同样创建要素数据集和要素类，并进行复制追加或合并复制。1、输入要合并的GDB数据所在的文件夹。注意些文件夹下的所有gdb文件都会被读到，包括子文件夹下的。2、输入合并后的GDB文件名，不需要输入【.gdb】。数据是完整的，但使用的时候要一个一个拖进地图中，进行分析的时候也需要将其合并后使用。如上图所示，在【数据处理】组—【GDB相关】面板下，点击【合并gdb数据库】工具。主要的工作反而是在分析数据结构。
在各数据库中使用 MERGE 实现插入避重 SQL
weixin_45626288的博客
07-02 1395
MERGE是一种在数据库管理系统中用于合并（插入、更新或删除）数据的SQL语句。它允许根据指定的条件将数据从一个表合并到另一个表中，同时避免重复插入或更新数据。MERGE语句通常由以下几个关键字和子句组成：MERGE：关键字指示要执行合并操作。INTO：指定目标表，即要将数据合并到其中的表。USING：指定源表，即提供要合并的数据的表。ON：指定用于匹配目标表和源表的条件。通常，使用一组或多组字段进行条件匹配。
Android 数据库 DBFlow 使用教程
anymyna的专栏
08-28 1695
github: https://github.com/anymyna/ui 步骤： 1、 在项目下的build.gradle中添加 allprojects { repositories { google() jcenter() maven { url "https://www.jitpack.io" } } } 2、 在app下的...
mysql 数据库合并命令_mysql多源复制及合并数据库
weixin_36125445的博客
02-18 1082
背景:机器1:10.1.6.99机器2:10.1.6.100机器3:10.1.6.1011、分别在三台主机上安装mysql数据库1)配置yum源[root@master lucky-front]# cat /etc/yum.repos.d/mysql-community.repo[mysql-connectors-community]name=MySQL Connectors Communityb...
SQL中MERGE的用法
SQL数据库开发
08-16 1万+
点击关注上方“SQL数据库开发”，设为“置顶或星标”，第一时间送达干货SQL专栏SQL基础知识第二版SQL高级知识第二版MERGE的定义MERGE关键字是一个神奇的DML关键字，它能将IN...
MySQL 两个数据库表中合并数据
aini4568的博客
03-29 4335
两个数据库表中合并数据 如果有 t1 和 t2 两个数据库表格，它们两个对应的字段是相同的。如何将 t2 的数据插入到t1中去呢？ insert into t1 select * from t2 INSERT INTO domestic_auction_artron_2018_detail_info_1_4_all SELECT *FROM domestic...
使用SQL语句将数据库中的两个表合并成一张表
热门推荐
caoyanchao1的博客
01-18 2万+
select * into 新表名 from (select * from T1 union all select * from T2) 这个语句可以实现将合并的数据追加到一个新表中。 不合并重复数据 select * from T1 union all select * from T2 合并重复数据 select * from T1 union select * from T2 两个表，表1 表2 如果要将 表1的数据并入表2用以下语句即可 insert into 表2(字段1,字段
mysql 合并数据库
05-17
1. 创建一个新的 MySQL 数据库，用于存储合并后的数据。 2. 将第一个数据库中的所有表复制到新数据库中。可以使用以下命令： ``` mysqldump -u username -p database1 | mysql -u username -p database_new ```...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值