Oracle Merge Statement

• You can sync different tables
• Normalize test data
• Transfer data between databases
• Insert or Update one table. In this trivial case where TARGET' and 'SOURCE is identical.

• It has it's limitations when used with a generic select from dual.

Syntax

   MERGE INTO target_table_name [alias]
   USING (source table or subquery) [alias]
   ON (condition)
   WHEN MATCHED THEN 
        UPDATE set clause
        DELETE where clause
   WHEN NOT MATCHED THEN 
        INSERT set clause

• MERGE INTO defines the target table where data is inserted
• USING' defines the source table or query which is different in complex applications of 'MERGE
• ON defines the match predicate to decide if the row considered present in the target table
• WHEN MATCHED THEN' yes it is there then either '''UPDATE''' or 'DELETE
• WHEN NOT MATCHED THEN ' then we 'INSERT

Samples

Insert or Update in one statement

 set define off
 MERGE INTO DEPARTMENT TRG
 USING DEPARTMENT SRC
 ON ( TRG.ID = 57)
 WHEN NOT MATCHED THEN
     INSERT (
           ID
         , DESC
         , CRT
 ) VALUES (
           57
         , 'Finance'
         , sysdate
 )
 WHEN MATCHED THEN
     UPDATE SET
           TRG.DESC = 'Finance'
         , TRG.CRT = sysdate
 ;

Replicate sample data from production

 set define off
 MERGE INTO DEPARTMENT TRG
 USING (
    select 20 as ID ,'Human Resources' as DESC, TO_DATE('15.11.2008', 'DD.MM.YYYY') as CRT  from dual  union all
    select 34 as ID ,'Research and Development' as DESC, TO_DATE('04.11.2008', 'DD.MM.YYYY') as CRT  from dual  union all
    select 56 as ID ,'Marketing' as DESC, TO_DATE('17.11.2008', 'DD.MM.YYYY') as CRT  from dual  union all
    select 73 as ID ,'Sales' as DESC, TO_DATE('19.11.2008', 'DD.MM.YYYY') as CRT  from dual  
  ) SRC
 ON (
     SRC.ID = TRG.ID
 )
 WHEN NOT MATCHED THEN
     INSERT (
           ID
         , DESC
         , CRT
 ) VALUES (
           SRC.ID
         , SRC.DESC
         , SRC.CRT
 )
 WHEN MATCHED THEN
     UPDATE SET
           TRG.DESC = SRC.DESC
         , TRG.CRT = SRC.CRT
 ;

Migration data for an product upgrade

 MERGE INTO DEPARTMENT TRG
 USING ( select  ID, as DESC, sysdate from dblink.DEPS) SRC
 ON ( SRC.DESC = TRG.DESC)
 WHEN NOT MATCHED THEN
     INSERT (
           ID
         , DESC
         , CRT
 ) VALUES (
           SRC.ID
         , SRC.DESC
         , SRC.CRT
 )
 WHEN MATCHED THEN
     UPDATE SET TRG.ID = SRC.ID , TRG.CRT = SRC.CRT
 ;

Sync to tables

 MERGE INTO DEPARTMENT TRG
 USING ( 
           select  ID, DESC, sysdate , 'U' as action from dblink.DEPS union all
           select  1, DESC, TO_DATE('04.11.2008', 'DD.MM.YYYY'), 'D' as action from departments 

           select  1, DESC, TO_DATE('04.11.2008', 'DD.MM.YYYY'), 'D' as action from dblink.DEPS
    ) SRC
 ON ( SRC.DESC = TRG.DESC)
 WHEN NOT MATCHED THEN
     INSERT (
           ID
         , DESC
         , CRT
 ) VALUES (
           SRC.ID
         , SRC.DESC
         , SRC.CRT
 )
 WHEN MATCHED THEN
     UPDATE SET TRG.ID = SRC.ID , TRG.CRT = SRC.CRT
     DELETE WHERE  action = 'D'
 ;

Single Row History copy

 MERGE INTO HISTORY TRG
 USING ( 
           select  contract_ID,  sysdate,  seq from DEPARTMENTS union all
    ) SRC
 ON ( SRC.contract_id = TRG.contract_id)
 WHEN NOT MATCHED THEN
     INSERT (
           ID
         , DESC
         , CRT
 ) VALUES (
           SRC.ID
         , SRC.DESC
         , SRC.CRT
 )
 WHEN MATCHED THEN
     UPDATE SET TRG.ID = SRC.ID , TRG.CRT = SRC.CRT
 ;