MERGE (SQL)

MERGE (SQL) is used to combine the data of multiple tables. It combines the INSERT and UPDATE elements. It is defined in the SQL:2003 standard. DB2, Oracle, and SQL Server 2008 support the use of the MERGE statement for updating or inserting rows, depending on whether they already exist in the target table.

Example 1:

The following query will merge data from employee table into the old_employee table. If the data already exists for an employee in old_employee table, it will update data for columns fname and salary. Otherwise it will create new row in old_employee table with value from employee table for columns ssn,lname,fname,salary,hiredate,deptno.

MERGE INTO old_employee oldemp
     USING (SELECT * FROM employee) emp
        ON (emp.ssn = oldemp.ssn)
WHEN MATCHED
THEN
   UPDATE SET oldemp.fname = emp.fname, oldemp.salary = emp.salary
WHEN NOT MATCHED
THEN
   INSERT     (ssn,
               lname,
               fname,
               salary,
               hiredate,
               deptno)
       VALUES (emp.ssn,
               emp.lname,
               emp.fname,
               emp.salary,
               emp.hiredate,
               emp.deptno);

Oracle allows you to place WHERE conditions on both the UPDATE and INSERT operations. In addition, Oracle allows you to specify rows to be deleted following an UPDATE operation:

Example 2:

MERGE INTO old_employee oldemp
     USING (SELECT * FROM employee) emp
        ON (emp.ssn = oldemp.ssn)
WHEN MATCHED
THEN
   UPDATE  SET oldemp.fname = emp.fname, oldemp.salary = emp.salary
           WHERE emp.fname IS NOT NULL
   DELETE
           WHERE oldemp.fname IS NULL
WHEN NOT MATCHED
THEN
    INSERT     (ssn,             
               lname,
               fname,
               salary,
               hiredate,
               deptno)
       VALUES (emp.ssn,
               emp.lname,
               emp.fname,
               emp.salary,
               emp.hiredate,
               emp.deptno);
        WHERE emp.fname IS NOT NULL;

This statement uses WHERE emp.fname IS NOT NULL to prevent updating any name to a null. The subsequent DELETE WHERE clause then deletes any updated rows that have null value in for fname in old_employee table.

Note:  DELETE WHERE a post-update deletion is. Rows not updated by the MERGE statement are not candidates for deletion. In DB2, you can specify a DELETE as the statement for a WHEN MATCHED clause. DB2 also allows more than one occurrence of the WHEN MATCHED and WHEN NOT MATCHED clauses.