Have MERGE remove records from Target that are not in the Source – Oracle 10g

The Oracle 10g Database release saw an extension of the MERGE statement
with a DELETE clause. That by the way makes MERGE the only statement that can fire
all three Statement Level triggers: Insert, Update, Delete. And it will make Oracle glad that back in 2003 when it discussed the new UPSERT command in the ANSI/ISO SQL standards comittee, it came out being called MERGE instead of UPSERT.

The DELETE clause of the MERGE statement will only ever act on records that were MATCHED and updated. That makes the following requirement a bit of a challenge: “I have a corporate enterprise level table with HRM data (called EMP). We receive a weekly feed of fresh employee-data from external systems, loaded into table NEW_EMPS (note: that could be an EXTERNAL TABLE, with the data feed being a text file). We need to update the Corporate HRM table EMP with the fresh feed in NEW_EMPS:

• new records in NEW_EMPS represent newly hired employees; these should be created in the EMP table
• records in NEW_EMPS with a corresponding (matched by EMPNO) record in EMP represented changed Employees records – new job, salary or deptno; the EMP records should be updated from their matches in NEW_EMPS
• finally, we have a special requirement – well, actually two:

• when an Employees was fired, he will be in the NEW_EMPS table with his or her job set to FIRED; the corresponding record in the EMP table should be removed
• we also have records in the EMP table for temporary employees (TEMPs); their Job is set to TEMP; the temporary records do not get fed from the external systems, they will never appear in NEW_EMPS. Whenever we refresh the EMP table from NEW_EMPS, we want all TEMPs to be removed from the EMP table

This last bullet presents the real challenge: how do we get the MERGE operation to remove records that are not in the Source table?

The first step is to create a MERGE statement that does the normal operation: refresh records from NEW_EMPS into EMP, and remove records that after MATCH-UPDATE have job equal to FIRED. That is straightforward:

merge into emp
using new_emps src
on (emp.empno = src.empno)
when matched
then
update set sal = src.sal
, job = src.job
, deptno = src.deptno
delete where job = 'FIRED'
when not matched
then
insert (empno, sal, ename, job, deptno)
values (src.empno, src.sal, src.ename, src.job, src.deptno)
/

This statement will insert new employees from NEW_EMPS into EMP, update existing Employees and remove Employees whose job is set to FIRED.

Deleting records from EMP with JOB=TEMP that are not in NEW_EMPS can not be achieved with that MERGE operation.

Now the last step can be made when we realize that the source for a MERGE operation does not have to be a table: it can be an inline query as well. So if we write a query that provides not just the records from NEW_EMPS but also the records in EMP that are eligible for deletion, we can handle the entire refresh in a single statement.

The following query will return all NEW_EMPS as well as the TEMP employees from EMP:

select nvl(temps.empno, new.empno) empno
, new.ename
, new.deptno
, new.sal
, case
when new.rowid is null -- for existing records in target not matched from new
then 'KILL'
else new.job
end job
from new_emps new
full outer join
( select *
from emp
where job = 'TEMP'
) temps
on (new.empno = temps.empno)
/

This query outer joins NEW_EMPS with sub-set of TEMPs in EMP. Whenever we have a TEMP record that is not present in NEW_EMPS, the CASE statement will return the value KILL for the job column, signalling a burning desire to remove the particular employee.

Using this query inside the MERGE statement, it becomes relatively simple to have the MERGE remove records that were never present in the NEW_EMPS table – but now have been made available in the SOURCE of the MERGE.

merge into emp
using ( select nvl(temps.empno, new.empno) empno
, new.ename
, new.deptno
, new.sal
, case
when new.rowid is null -- for existing TEMP records in emp not matched from new
then 'KILL'
else new.job
end job
from new_emps new
full outer join
( select *
from emp
where job = 'TEMP'
) temps
on (new.empno = temps.empno)
) src
on (emp.empno = src.empno)
when matched
then
update set sal = src.sal
, job = src.job
delete where job in ('KILL','FIRED')
when not matched
then
insert (empno, sal, ename, job, deptno)
values (src.empno, src.sal, src.ename, src.job, src.deptno)

Resources

Oracle Documentation (10.2) for MERGE