1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

MERGE : DML Magic in SQL

Discussion in 'SQL PL/SQL' started by rajavu, Nov 26, 2008.

  1. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    MERGE statements was introduced in Oracle 9i . This command was beneficial for the ETL (Extraction, Transformation, and Loading) part of any Business Intelligence process flow and for the performance improvement. Oracle claim it will improve the performance.

    In Oracle 9i i was intorduced for conditional insert and update . But form Oracle 10 , its can have all DML statements ;

    INSERT
    UPDATE
    DELETE

    MERGE in Oracle 9i

    In Oracle 9i, it combined the conditional INSERT and UPDATE commands in a single statement . This operation was so called UPSERT. It will be executed on meeting some criteria.

    Prior to Oracle 9i , the same operation had to be carried out as insert and update statements Or loop through the records in PL/SQL context.

    The Syntax of MERGE statement in Oracle 9i is as shown below

    Code (Text):

    MERGE INTO <table_name>
    USING      <table_or_view>
    ON         (<condition>)
    WHEN MATCHED THEN
               <UPDATE SET table_name.col_name =  table_or_view.col_name>
    WHEN NOT MATCHED THEN
               <INSERT (clo_list) VALUES (value_list)>
     
    The MATCHED and NOT MATCHED clauses are mandatory for Merge in Oracle 9i.

    Lets see the initial values in the Demo tables before merging.

    Code (Text):

    SQL> SELECT EMPNO,ENAME,DEPTNO,SAL FROM  EMP_MGR;

         EMPNO ENAME          DEPTNO        SAL
    ---------- ---------- ---------- ----------
          7369 SMITH              20        800
          7499 ALLEN              30       1600
          7521 WARD               30       1250
          7566 JONES              20       2975
          7654 MARTIN             30       1250
          7698 BLAKE              30       2850
          7782 CLARK              10       2450
          7788 SCOTT              20       3000
          7839 KING               10       5000
          7844 TURNER             30       1500
          7876 ADAMS              20       1100
          7900 JAMES              30        950
          7902 FORD               20       3000
          7934 MILLER             10       1300
          2000 RAJUVAN            50       6000

    15 rows selected.

    SQL> SELECT * FROM DEPT_MAST;

        DEPTNO DNAME          LOC            EMP_COUNT
    ---------- -------------- ------------- ----------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL>
     
    Here , it is to be noted that Department 40 is present in DEPT_MAST but not in EMP_MGR and Department 50 is presnt in EMP_MGR but not in DEPT_MAST.

    Now merge will try to Update the EMP_COUNT in DEPT_MAST with number of employees from the corresponding departments in EMP_MGR and also insert default values if Department is not found in DEPT_MAST.

    Code (Text):

    SQL> SELECT DEPTNO , COUNT(*) CNT
      2                 FROM   EMP_MGR
      3                 GROUP BY DEPTNO;

        DEPTNO        CNT
    ---------- ----------
            30          6
            20          5
            50          1
            10          3

    SQL> MERGE INTO DEPT_MAST D
      2  USING (SELECT   DEPTNO , COUNT(*) CNT
      3         FROM     EMP_MGR
      4         GROUP BY DEPTNO) E
      5  ON    (E.DEPTNO =D.DEPTNO)
      6  WHEN MATCHED THEN
      7        UPDATE SET D.EMP_COUNT = E.CNT
      8  WHEN NOT MATCHED THEN
      9        INSERT VALUES (E.DEPTNO,'IT','BANGALORE',E.CNT);

    4 rows merged.

    SQL> SELECT * FROM DEPT_MAST;

        DEPTNO DNAME          LOC            EMP_COUNT
    ---------- -------------- ------------- ----------
            10 ACCOUNTING     NEW YORK               3
            20 RESEARCH       DALLAS                 5
            30 SALES          CHICAGO                6
            40 OPERATIONS     BOSTON
            50 IT             BANGALORE              1

    SQL> rollback;

    Rollback complete.

    SQL>
     
    Now you can see that departments 10,20 and 30 are updated and department 50 is inserted. ie Altogether 4 merges ( 3 update , 1 insert ) Also note that department 40 is not updated there is no employee in the EMP_MGR table for department 40.

    [PAGE]MERGE in Oracle 10g[/PAGE]
    MERGE in Oracle 10g

    In Oracle 10g, MERGE statement was enhanced with the following features

    Optional MATCHED clause

    On one of MATHCHED and NOT MATCHED clauses is mandatory. Merge can either use MATCHED or NOT MATCHED clause or both.

    Introduction of DELETE

    Merge is enhanced with DELETE statment in 10g. MERGE is no longer just UPSERT statement.

    Optional WHERE clause

    Merge statement can have optional WHERE clause along with DML statemets inside it (Insert/Update/Delete)


    The Syntax of MERGE statement in Oracle 10g is as shown below

    Code (Text):

    MERGE INTO <table_name>
    USING      <table_or_view>
    ON         (<condition>)
    [WHEN MATCHED THEN
               <UPDATE SET table_name.col_name =  table_or_view.col_name> [Where clause]
               <DELETE <Where clause> > ]
    [WHEN NOT MATCHED THEN
               <INSERT (clo_list) VALUES (value_list)> [Where clause]]
     

    Lets see the initial values in the demo tables before merging.

    Code (Text):

    SQL> SELECT EMPNO,ENAME,DEPTNO,SAL FROM  EMP_MGR;

         EMPNO ENAME          DEPTNO        SAL
    ---------- ---------- ---------- ----------
          7369 SMITH              20        800
          7499 ALLEN              30       1600
          7521 WARD               30       1250
          7566 JONES              20       2975
          7654 MARTIN             30       1250
          7698 BLAKE              30       2850
          7782 CLARK              10       2450
          7788 SCOTT              20       3000
          7839 KING               10       5000
          7844 TURNER             30       1500
          7876 ADAMS              20       1100
          7900 JAMES              30        950
          7902 FORD               20       3000
          7934 MILLER             10       1300
          2000 RAJUVAN            50       6000

    15 rows selected.

    SQL> SELECT * FROM DEPT_MAST;

        DEPTNO DNAME          LOC            EMP_COUNT
    ---------- -------------- ------------- ----------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL>
     
    Here , it is to be noted that Department 40 is present in DEPT_MAST but not in EMP_MGR and Department 50 is presnt in EMP_MGR but not in DEPT_MAST.

    Now merge will try to Update the EMP_COUNT in DEPT_MAST with number of employees from the corresponding departments in EMP_MGR ,insert record with default value if Department is not found in DEPT_MAST and delete the department if there is no employee in EMP_MGR for a particular department

    Code (Text):

    SQL> SELECT      NVL(E.DEPTNO,D.DEPTNO) , COUNT(EMPNO) CNT
      2  FROM        EMP_MGR E FULL OUTER JOIN DEPT_MAST D
      3  ON          E.DEPTNO = D.DEPTNO
      4  GROUP BY    NVL(E.DEPTNO,D.DEPTNO);

    NVL(E.DEPTNO,D.DEPTNO)        CNT
    ---------------------- ----------
                        30          6
                        20          5
                        50          1
                        40          0
                        10          3

    SQL> MERGE INTO DEPT_MAST D
      2  USING  ( SELECT    NVL(E.DEPTNO,D.DEPTNO) DEPTNO , COUNT(EMPNO) CNT
      3           FROM      EMP_MGR E FULL OUTER JOIN DEPT_MAST D
      4           ON        E.DEPTNO = D.DEPTNO
      5           GROUP BY  NVL(E.DEPTNO,D.DEPTNO)) E
      6  ON   ( D.DEPTNO  =E.DEPTNO  )
      7  WHEN MATCHED THEN
      8       UPDATE SET D.EMP_COUNT = E.CNT
      9       DELETE WHERE  E.CNT= 0
     10  WHEN NOT MATCHED THEN
     11       INSERT VALUES (E.DEPTNO,'IT','BANGALORE',E.CNT) ;

    5 rows merged.

    SQL> SELECT * FROM DEPT_MAST;

        DEPTNO DNAME          LOC            EMP_COUNT
    ---------- -------------- ------------- ----------
            10 ACCOUNTING     NEW YORK               3
            20 RESEARCH       DALLAS                 5
            30 SALES          CHICAGO                6
            50 IT             BANGALORE              1

    SQL> rollback;

    Rollback complete.

    SQL>
     
    Now you can see that depatments 10,20 and 30 are updated , department 50 is inserted and department 40 is deleted. ie Altogether 5 merges ( 3 updates , 1 insert , 1 delete ).

    [PAGE]MERGE in Oracle 8i[/PAGE]
    MERGE in Oracle 8i

    Interestingly lets check out the equivalent SQL queries required to achieve the same result of the last MERGE statement.

    Code (Text):

    SQL> MERGE INTO DEPT_MAST D
      2  USING  ( SELECT    NVL(E.DEPTNO,D.DEPTNO) DEPTNO , COUNT(EMPNO) CNT
      3           FROM      EMP_MGR E FULL OUTER JOIN DEPT_MAST D
      4           ON        E.DEPTNO = D.DEPTNO
      5           GROUP BY  NVL(E.DEPTNO,D.DEPTNO)) E
      6  ON   ( D.DEPTNO  =E.DEPTNO  )
      7  WHEN MATCHED THEN
      8       UPDATE SET D.EMP_COUNT = E.CNT
      9       DELETE WHERE  E.CNT= 0
     10  WHEN NOT MATCHED THEN
     11       INSERT VALUES (E.DEPTNO,'IT','BANGALORE',E.CNT) ;

    5 rows merged.

    SQL>
     

    Lets query the Initial values first.

    Code (Text):

    SQL> SELECT * FROM DEPT_MAST;

        DEPTNO DNAME          LOC            EMP_COUNT
    ---------- -------------- ------------- ----------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL>

    One update statement for updating only depatments 10,20 and 30.

    Code (Text):

    SQL>  UPDATE DEPT_MAST D
      2   SET    EMP_COUNT =(SELECT    COUNT(*) CNT
      3                 FROM      EMP_MGR E
      4                 WHERE     E.DEPTNO = D.DEPTNO)
      5   WHERE EXISTS  (SELECT    NULL
      6             FROM      EMP_MGR E
      7             WHERE     E.DEPTNO = D.DEPTNO)
      8  ;

    3 rows updated.

    SQL>
     

    One insert statement to insert the record for department 50.

    Code (Text):

    SQL> INSERT INTO DEPT_MAST
      2  SELECT DEPTNO,'IT','BANGALORE',
      3         (SELECT    COUNT(*) CNT
      4                 FROM      EMP_MGR E1
      5                 WHERE     E.DEPTNO = E1.DEPTNO)
      6  FROM EMP_MGR E
      7  WHERE not EXISTS  (SELECT    NULL
      8                FROM      DEPT_MAST d
      9                WHERE     E.DEPTNO = D.DEPTNO)
     10  ;

    1 row created.

    SQL>
     
    One delete statement to delete the vacant department 40.

    Code (Text):

    SQL> DELETE FROM DEPT_MAST E
      2  WHERE not EXISTS  (SELECT    NULL
      3                FROM      EMP_MGR d
      4                WHERE     E.DEPTNO = D.DEPTNO)
      5  ;

    1 row deleted.

    SQL>
     
    Now , its time to query the department .

    Code (Text):

    SQL> SELECT * FROM DEPT_MAST;

        DEPTNO DNAME          LOC            EMP_COUNT
    ---------- -------------- ------------- ----------
            10 ACCOUNTING     NEW YORK               3
            20 RESEARCH       DALLAS                 5
            30 SALES          CHICAGO                6
            50 IT             BANGALORE              1

    SQL> rollback;

    Rollback complete.

    SQL>
     
    See . How elegant is the new MERGE approach !!!

    Alternatively, you could create a procedural implementation. It would need to check every load record against the target to find if the record already exists; only then could it decide whether to insert , delete or update the data.

    Both of these approaches suffer from deficiencies in performance and usability. The new MERGE command overcomes these deficiencies, processing the conditional INSERT-UPDATE-DELETE within a single statement. The data is scanned only once, and the appropriate DML command is issued, either serially or in parallel.
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    thanq raj. i dint find clear merge stmt upto now. lucidly explained what is merge . thanq very much