Merge/ Upsert Sql introduced with Oracle 9i

In version 9i, Oracle added a new DML command, MERGE. This command was improved in 10g. MERGE is generally associated with Data Warehouse and ETL but truthfully, it can be used anywhere you need to move data from one table to another. The MERGE command, also called an UPSERT, is a combination of INSERT and UPDATE.

The basic syntax of a MERGE is:


MERGE INTO destination_table dest
USING (SELECT col1, col2, col3 FROM source_table) source
ON (dest.col1 = source.col1)
WHEN MATCHED THEN
UPDATE SET dest.col2 = source.col2,
dest.col3 = source.col3
WHEN NOT MATCHED THEN
INSERT (dest.col1, dest.col2, dest.col3)
VALUES (source.col1, source.col2, source.col3)
/

That is a basic MERGE. The WHEN MATCHED clause as well as the WHEN NOT MATCHED clause is optional. You can leave off the WHEN MATCHED if you need to insert records and there might be dupes.

MERGE INTO designates the table where the data is going. USING selects data from a table (or tables, or even a view). ON is the matching criteria. WHEN MATCHED means that a match was found (UPDATE) and WHEN NOT MATCHED a match was not found (INSERT).


Good Example 1

The following example will MERGE values from the table DEPT_ONLINE into a table called DEPT:
SQL> SELECT * FROM dept;

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

SQL> SELECT * FROM dept_online;

DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
20 RESEARCH DEV DALLAS
50 ENGINEERING WEXFORD


SQL> MERGE INTO dept d
USING (SELECT deptno, dname, loc
FROM dept_online) o
ON (d.deptno = o.deptno)
WHEN MATCHED THEN
UPDATE SET d.dname = o.dname, d.loc = o.loc
WHEN NOT MATCHED THEN
INSERT (d.deptno, d.dname, d.loc)
VALUES (o.deptno, o.dname, o.loc);

3 rows merged.


SQL> SELECT * FROM dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DEV DALLAS
30 SALES CHICAGO
50 ENGINEERING WEXFORD
60 HELP DESK PITTSBURGH
40 OPERATIONS BOSTON

6 rows selected.