Recipe4.11.Merging Records

Recipe 4.11. 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 < 2000) 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.