Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / PostgreSQL
 

Merge Data in Database

4.20/5 (3 votes)
28 Jun 2022CPOL3 min read 6.7K   88  
How to use MERGE statements in different databases
This article explores how to use these MERGE statements in different databases. We will check other alternative approaches, as all databases and versions do not support the MERGE statement.

Introduction

MERGE statement is a very popular clause that can manage inserts, updates, and deletes, all in a single transaction. Here, in this article, we will check how to use these MERGE statements in different databases. We will check other alternative approaches, as all databases and versions do not support the MERGE statement. The focus is on exploring the syntax/use differences considering different databases, with minimal explanations.

Background

Suppose we have two tables called source and target and we need to update the target table based on the values matched from the source table.

Now the cases are:

  1. The source table has some rows that do not exist in the target table. In this case, we need to add rows that are in the source table into the target table.
  2. The source table has some rows with the same keys as the rows in the target table. However, these rows have different values in other columns. In this case, we need to update them in the target table with the values coming from the source table.
  3. The target table has some rows that do not exist in the source table. In this case, we need to delete these rows from the target table.

What Do We Need?

  • Unique identifier/logic to identify each row
    • Primary key
    • Composite key
    • Unique column or combinations of columns
  • Data change indicator to detect data changes
    • Row version
    • Last modified or created date time indicator
    • Uniquely valued data column/columns

In most cases, we may not need to consider any data changes. So a unique/any identifier/logic is just enough.

Current Example

In our current scenario, we have an Id column, which is the primary key to uniquely identifying each row, and a UpdatedDateTime column to trace new data changes.

SQL Server

Table and Data

Let's create tables and insert data:

SQL
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);

Using MERGE Statement

The SQL Server's merge statement is very straightforward.

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

The source table is not necessary to be an actual table, we can also use inline data as below:

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

Using Regular INSERT UPDATE DELETE Statement

  • Delete Rows section is going to delete unnecessary rows from the user table
  • Update Rows section will update the rows of the user table with updated data
  • Add Rows going to add new rows to the user table
SQL
-------------------------------- 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

Table and Data

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

Using MERGE Statement

This merge statement is a bit different than the SQL Server one.

SQL
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);

The DELETE thing is actually a part of the MATCHED and UPDATE section. So to DELETE something first, we need to get the matched row and after updating the row, we have to decide if we should delete it now or let it be as it is.

Using Regular INSERT UPDATE DELETE Statement

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

Table and Data

SQL
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);

Using MERGE Statement

No MERGE statement is available.

Using Regular INSERT UPDATE DELETE Statement

SQL
################ 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
);

While working with MySQL 8, the update rows query wasn't working as expected. As from version 5.7, SELECT FROM and UPDATE the same table not working. So changing the query a bit.

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

Table and Data

SQL
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);

Using MERGE Statement

No MERGE statement is available.

Using Regular INSERT UPDATE DELETE Statement

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

Table and Data

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

Using MERGE Statement

No MERGE statement is available.

Using Regular INSERT UPDATE DELETE Statement

SQL
-------------------------------- 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
);

Others Helper Queries

Here are some other helper queries.

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

The below query can only be used in the SQL Server database.

SQL
/*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;

Important Things

  • MERGE statement manages inserts, updates, and deletes all in a single TRANSACTION
  • Should use TRANSACTION statement while Using Regular INSERT UPDATE DELETE Statements

TRANSACTION statement example for SQL Server database:

SQL
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

Limitations

Things may vary depending on database versions.

My working database versions are:

  • Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
  • Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  • MySQL 5.5.61
  • PostgreSQL 10.5, compiled by Visual C++ build 1800, 64-bit

History

  • 29th June, 2022: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Permalink

Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Article Copyright 2022 by DiponRoy
Everything else Copyright © CodeProject, 1999-2024

Web01 2.8:2024-10-20:1