Synchronize 2 tables with a SINGLE SQL statement in Oracle, by Zahar Hilkevich

To illustrate the challenge we will use dept table from Oracle’s default scott schema and its copy, dept2 table. Here is the content:

SELECT *
FROM dept;

DEPTNO DNAME          LOC
------ -------------- --------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON

SELECT *
FROM dept2;

DEPTNO DNAME          LOC
------ -------------- ---------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          LAS VEGAS
    50 HR             MIAMI

As you can see, in the dept2 table, 1 original row was deleted (deptno=40), 1 – updated (location changed for deptno=30) and 1 – inserted (deptno=50).

The challenge is to present a single SQL DML statement against dept2 table so its content would become identical with the dept table.

As you could have guessed already, it must be a MERGE statement:

MERGE INTO dept2 d2
USING (SELECT deptno, dname, loc
       FROM dept
       UNION ALL
       SELECT a.deptno, a.dname, 'DELETE ME' AS loc
       FROM dept2 a LEFT JOIN dept b ON a.deptno=b.deptno
       WHERE b.deptno IS NULL) d
ON (d2.deptno=d.deptno)
WHEN NOT MATCHED THEN
   INSERT(deptno, dname, loc)
   VALUES(d.deptno, d.dname, d.loc)
WHEN MATCHED THEN
   UPDATE SET d2.dname=d.dname,
              d2.loc=d.loc
   DELETE WHERE d2.loc='DELETE ME'

5 rows merged.

The main trick here is to SELECT all rows (using both copies of dept table) that will include rows to be updated, inserted, and deleted:

SELECT deptno, dname, loc
FROM dept
UNION ALL
SELECT a.deptno, a.dname, 'DELETE ME' AS loc
FROM dept2 a LEFT JOIN dept b ON a.deptno=b.deptno
WHERE b.deptno IS NULL

Result:

DEPTNO DNAME          LOC
------ -------------- ---------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    50 HR             DELETE ME

Once we have this, the rest is relatively simple: all these records will be either inserted (deptno=40) or updated (the rest) and only one of them (deptno=50) will be deleted using the indicator we created (loc=’DELETE ME’).

DELETE option in the MERGE command will only affect records updated by the UPDATE clause. That is why we need to include it in the list and make sure we can identify such record using a reliably criteria (loc=’DELETE ME’).

For more details on how MERGE works and for a variety of other tricks and workarounds, check my book “Oracle SQL Tricks and Workarounds”

Advertisements

One thought on “Synchronize 2 tables with a SINGLE SQL statement in Oracle, by Zahar Hilkevich

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s