oracle SQL:Merge Statements

10/15/2015 07:37:00 AM

The MERGE statement was introduced in Oracle 9i to conditionally insert or update data depending on its presence, a process also known as an "upsert". The MERGE statement reduces table scans and can perform the operation in parallel if required.

Syntax

Consider the following example where data from the HR_RECORDS table is merged into the EMPLOYEES table.

MERGE INTO emp e
    USING dept h
    ON (e.deptno = h.deptno)
  WHEN MATCHED THEN
    UPDATE SET e.sal = h.sal
  WHEN NOT MATCHED THEN
    INSERT (10,'XYZ')
    VALUES (h.empno, e.ename);