Oracle MERGE Statement - Quick Catch Up

In this article we have to describe the Oracle merge statement.


MERGE is a DML command that enables us to optionally insert or update data into a target table, depending upon whether matching records already exist.

The MERGE statement is also known as UPSERT i.e. combination of UPDATE and INSERT. The MERGE statement is the best way to combine multiple operations, to avoid multiple INSERT and UPDATE.

One of the big advantages of the MERGE statement is that the entire data are read and processed only once.

Syntax:

SQL>      MERGE
                                INTO dbo.TargetTable tgt    -- Target Table
                                USING dbo.SourceTable src   -- Source Table
                                ON tgt.ID = src.ID          -- Main comparison criteria
                WHEN MATCHED                                 -- When ID's exist in both tables
                                THEN
                                -- DO SOMETHING
                WHEN NOT MATCHED                    -- When ID's from Source do not exist in Target
                                THEN
                                -- DO SOMETHING
                WHEN NOT MATCHED BY SOURCE          -- When ID's from Target do not exist in Source
                                THEN
                                -- DO SOMETHING

Example :

Now we want to update the dept table from the dept_online table i.e. those rows which are already present in the dept table and the latest data from the dept_online table should be updated and those rows which are not present in dept table should be inserted.

SQL> SELECT * FROM dept;

    DEPTNOu        DNAME                  COUNTRY
        10             ACCOUNTING             USA        
        20             RESEARCH TEAM        INDIA
        30             SALES                           UK
        60             HELP                            AMERICA


SQL> SELECT * FROM dept_online;

    DEPTNO         DNAME              COUNTRY
        40             OPERATIONS        USA
        20             RESEARCH               INDIA
        50             ENG                         ITALY
        60             HELP DESK              USA

SQL>    MERGE
                             INTO dept d
             USING 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);
4 rows merged.

SQL> SELECT * FROM dept;

    DEPTNO               DNAME                  COUNTRY
        10             ACCOUNTING         USA
        20             RESEARCH               INDIA
        30             SALES                  UK
        40             OPERATIONS             USA
        50             ENG                   ITALY
        60            HELP DESK              USA