Update a database table using MERGE.

Sometimes we need to update periodically a table reading data from a source (another table, a text file …).
Often happens that the source is “complete”, that is to say that it contains both records already loaded in the target table and new records. The already loaded records could need an update.
The simplest approach is the following: periodically empty the target table and refill it reading all the records from the source.
This is not always the best approach. What if we want to insert only new records and update those that are already loaded? We could use the MERGE statement.

Let’s see an example. We have to update the employees list (MYEMP table) reading the EMP table.


SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DIC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SET-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAG-81       2850                    30
      7782 CLARK      MANAGER         7839 09-GIU-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DIC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SET-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-GEN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DIC-81        950                    30
      7902 FORD       ANALYST         7566 03-DIC-81       3000                    20
      7934 MILLER     CLERK           7782 23-GEN-82       1300                    10

SQL> select * from myemp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       1000                    20
      7654 MARTIN     SALESMAN        7698 28-SET-81       1000       1400         30
      7698 BLAKE      MANAGER         7839 01-MAG-81       1000                    30
      7782 CLARK      MANAGER         7839 09-GIU-81       1000                    10
      7788 SCOTT      ANALYST         7566 09-DIC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SET-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-GEN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DIC-81        950                    30
      7902 FORD       ANALYST         7566 03-DIC-81       3000                    20
      7934 MILLER     CLERK           7782 23-GEN-82       1300                    10

As you can see, the MYEMP table misses a few records and some salaries are different from those of the EMP table.
Let’s load data using MERGE:


SQL> MERGE into MYEMP
  2  USING EMP
  3  ON (emp.empno=myemp.empno)
  4  WHEN MATCHED THEN
  5  UPDATE SET
  6    ename    = emp.ename,
  7    job      = emp.job,
  8    mgr      = emp.mgr,
  9    hiredate = emp.hiredate,
 10    sal      = emp.sal,
 11    comm     = emp.comm,
 12    deptno   = emp.deptno
 13  WHEN NOT MATCHED THEN
 14  INSERT (empno, ename, job, mgr, hiredate, sal, comm, deptno)
 15  VALUES (emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate,
 16          emp.sal, emp.comm, emp.deptno)
 17  ;

SQL> select * from myemp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SET-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAG-81       2850                    30
      7782 CLARK      MANAGER         7839 09-GIU-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DIC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SET-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-GEN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DIC-81        950                    30
      7902 FORD       ANALYST         7566 03-DIC-81       3000                    20
      7934 MILLER     CLERK           7782 23-GEN-82       1300                    10
      7369 SMITH      CLERK           7902 17-DIC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

Loading succeeded and now the two tables have the same content.
Let’s review the statement line by line:

  • MERGE into MYEMP
  • Indicates the table to update

  • USING EMP
  • Indicates the source table, what if we have to read a file? We always can see the file as a table using an external table.

  • ON (emp.empno=myemp.empno)
  • The condition to check, if this is true an UPDATE will be performed, otherwise an INSERT.

  • WHEN MATCHED THEN
  • When the above condition is true…

  • UPDATE SET etc…
  • …execute this UPDATE.

  • WHEN NOT MATCHED THEN
  • Else…

  • INSERT (empno, ename, job, mgr, hiredate, sal, comm, deptno) etc..
  • …execute this INSERT.

    There’s some clause not used in the example.

    You can add to the UPDATE clause a WHERE clause to update only some specific records. If, for example, we need to update the record only if the new salary is greater then the old one, we can do:

    
    MERGE into MYEMP                                              
    USING EMP                                                     
    ON (emp.empno=myemp.empno)                                    
    WHEN MATCHED THEN                                             
    UPDATE SET                                                    
      ename    = emp.ename,                                       
      job      = emp.job,                                         
      mgr      = emp.mgr,                                         
      hiredate = emp.hiredate,                                    
      sal      = emp.sal,                                         
      comm     = emp.comm,                                        
      deptno   = emp.deptno                                       
    WHERE sal < emp.sal
    WHEN NOT MATCHED THEN                                         
    INSERT (empno, ename, job, mgr, hiredate, sal, comm, deptno)  
    VALUES (emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, 
            emp.sal, emp.comm, emp.deptno)                        
    ;                                                             
                                                                  
    

    Furthermore you can specify a DELETE clause in the UPDATE.
    It’s useful to delete from the target table those records that, after the UPDATE, match the condition.
    For example we need to delete from MYEMP the employees that, after the update, have a salary smaller than 1000:

    
    SQL> MERGE into MYEMP
      2  USING EMP
      3  ON (emp.empno=myemp.empno)
      4  WHEN MATCHED THEN
      5  UPDATE SET
      6    ename    = emp.ename,
      7    job      = emp.job,
      8    mgr      = emp.mgr,
      9    hiredate = emp.hiredate,
     10    sal      = emp.sal,
     11    comm     = emp.comm,
     12    deptno   = emp.deptno
     13  DELETE WHERE sal < 1000
     14  WHEN NOT MATCHED THEN
     15  INSERT (empno, ename, job, mgr, hiredate, sal, comm, deptno)
     16  VALUES (emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate,
     17          emp.sal, emp.comm, emp.deptno)
     18  ;
    
    SQL>  select * from myemp;
    
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SET-81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAG-81       2850                    30
          7782 CLARK      MANAGER         7839 09-GIU-81       2450                    10
          7788 SCOTT      ANALYST         7566 09-DIC-82       3000                    20
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
          7844 TURNER     SALESMAN        7698 08-SET-81       1500          0         30
          7876 ADAMS      CLERK           7788 12-GEN-83       1100                    20
          7902 FORD       ANALYST         7566 03-DIC-81       3000                    20
          7934 MILLER     CLERK           7782 23-GEN-82       1300                    10
          7369 SMITH      CLERK           7902 17-DIC-80        800                    20
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
    
    

    Oracle first of all updated 14 rows, then deleted the row of the employee JAMES (gone from a salary of 1000 to a salary of 950).
    Note that the SMITH’s row has not been deleted. It has a salary of 800 but it was not present in MYEMP before the MERGE so it came in whit the INSERT, not with the UPDATE.
    The DELETE clause applies only to the update records.

    The INSER clause too can be enriched with a WHERE clause to insert only the records that match a given condition.

    For example, to insert in MYEMP only those employees having a salary greater than 1000:

    
    SQL> MERGE into MYEMP
      2  USING EMP
      3  ON (emp.empno=myemp.empno)
      4  WHEN MATCHED THEN
      5  UPDATE SET
      6    ename    = emp.ename,
      7    job      = emp.job,
      8    mgr      = emp.mgr,
      9    hiredate = emp.hiredate,
     10    sal      = emp.sal,
     11    comm     = emp.comm,
     12    deptno   = emp.deptno
     13  WHEN NOT MATCHED THEN
     14  INSERT (empno, ename, job, mgr, hiredate, sal, comm, deptno)
     15  VALUES (emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate,
     16          emp.sal, emp.comm, emp.deptno)
     17  WHERE emp.sal>1000
     18  ;
    
    SQL>  select * from myemp;
    
         EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7566 JONES      MANAGER         7839 02-APR-81       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SET-81       1250       1400         30
          7698 BLAKE      MANAGER         7839 01-MAG-81       2850                    30
          7782 CLARK      MANAGER         7839 09-GIU-81       2450                    10
          7788 SCOTT      ANALYST         7566 09-DIC-82       3000                    20
          7839 KING       PRESIDENT            17-NOV-81       5000                    10
          7844 TURNER     SALESMAN        7698 08-SET-81       1500          0         30
          7876 ADAMS      CLERK           7788 12-GEN-83       1100                    20
          7900 JAMES      CLERK           7698 03-DIC-81        950                    30
          7902 FORD       ANALYST         7566 03-DIC-81       3000                    20
          7934 MILLER     CLERK           7782 23-GEN-82       1300                    10
          7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
          7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
    
    

    This time SMITH has been excluded because he has a salary of 800. Please note that in the WHERE clause of the INSERT you cannot reference columns of the updating table, just columns of the source one.

    To finish, you can handle errors using the LOG ERRORS clause.

    Massimo.

    Advertisements

    Tags: , ,

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out /  Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    w

    Connecting to %s


    %d bloggers like this: