SQL Server : Merging Records

Problem

You want to conditionally insert, update, or delete records in a table depending on whether or not corresponding records exist. (If a record exists, then update; if not,then insert; if after updating a row fails to meet a certain condition, delete it.) For example, you want to modify table EMP_COMMISSION such that:

• If any employee in EMP_COMMISSION also exists in table EMP, then update their commission (COMM) to 1000.

• For all employees who will potentially have their COMM updated to 1000, if their SAL is less than 2000, delete them (they should not be exist in EMP_COMMISSION).

• Otherwise, insert the EMPNO, ENAME, and DEPTNO values from table EMP into table EMP_COMMISSION.

Essentially, you wish to execute either an UPDATE or an INSERT depending on whether a given row from EMP has a match in EMP_COMMISSION. Then you wish to execute a DELETE if the result of an UPDATE causes a commission that's too high.

The following rows are currently in tables EMP and EMP_COMMISSION, respectively:

 
 select deptno,empno,ename,comm
   from emp
  order by 1

 DEPTNO      EMPNO ENAME        COMM
 ------ ---------- ------ ----------
     10       7782  CLARK
     10       7839  KING
     10       7934  MILLER
     20       7369  SMITH
     20       7876  ADAMS
     20       7902  FORD
     20       7788  SCOTT
     20       7566  JONES
     30       7499  ALLEN        300
     30       7698  BLAKE
     30       7654  MARTIN      1400
     30       7900  JAMES
     30       7844  TURNER         0
     30       7521  WARD         500

 
 select deptno,empno,ename,comm
   from emp_commission
  order by 1

     DEPTNO      EMPNO ENAME            COMM
 ---------- ---------- ---------- ----------
         10       7782 CLARK
         10       7839 KING
         10       7934 MILLER

Solution

Oracle is currently the only RDBMS with a statement designed to solve this problem. That statement is the MERGE statement, and it can perform either an UPDATE or an INSERT, as needed. For example:

 1  merge into emp_commission ec
 2  using (select * from emp) emp
 3     on (ec.empno=emp.empno)
 4   when matched then
 5        update set ec.comm = 1000
 6        delete where (sal <>
 7   when not matched then
 8        insert (ec.empno,ec.ename,ec.deptno,ec.comm)
 9        values (emp.empno,emp.ename,emp.deptno,emp.comm)

Discussion

The join on line 3 of the solution determines what rows already exist and will be updated. The join is between EMP_COMMISSION (aliased as EC) and the subquery (aliased as emp). When the join succeeds, the two rows are considered "matched" and the UPDATE specified in the WHEN MATCHED clause is executed. Otherwise, no match is found and the INSERT in WHEN NOT MATCHED is executed. Thus, rows from table EMP that do not have corresponding rows based on EMPNO in table EMP_COMMISSION will be inserted into EMP_COMMISSION. Of all the employees in table EMP only those in DEPTNO 10 should have their COMM updated in EMP_COMMISSION, while the rest of the employees are inserted. Additionally, since MILLER is in DEPTNO 10 he is a candidate to have his COMM updated, but because his SAL is less than 2000 it is deleted from EMP_COMMISSION.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table