目录
介绍
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;
使用常规插入更新删除语句
- “删除行”部分将从用户表中删除不必要的行
- “更新行”部分将使用更新的数据更新用户表的行
- “添加行”将向用户表添加新行
-------------------------------- 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