Oracle Merge Statement

Contents
Oracles Merge statement is a great way to solve the classical Insert or Update dilema efficiently in on statement.
But it can do much more: Limits:

NOTE: Merge has limitation when using many ten thousands rows
select form .. dual union all as
source. It simple refuses to work. While it is nice to transfer test test data it gets also pretty large.


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

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 
minus
           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
 ;


Status: Published Date: 2017/06/05 14:14:14 Revision: 1.1

Copyright bei Andreas Haack (C) 2014. Diese Seite wird so wie sie ist zur Verfuegung gestellt, ohne irgenweche Garantien der Verwendbarkeit fuer bestimte Zwecke. Die auf dieser Seiten angebrachten Links liegen ausserhalb der redaktionellen Verantwortung von Andreas Haack und es wird keine Haftung oder Garantie uebernommen. Die Seiten sind Copyright (c) 2014 von Andreas Haack. Kein Teil darf ohne die schriftliche Einverstaendnis von Andreas Haack veroeffentlicht werden.
The page is provided 'as is' , without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fit- ness for a particular purpose and non-infringement. In no event shall Andreas Haack be liable for any claim, damages or other liability. This page is copyrighted property of Andreas Haack. Copyright by Andreas Haack (c) 2014 . No part of this page may be published without written permission for Andreas Haack. A hyper-link may created to this page but NOT to the embedded elements of this page. It may be freely downloaded for private purpose only as long as it is unaltered.