Oracle Merge Is Not Just For Merging

I have long liked the Oracle merge statement - so much more elegant (and efficient) than the "try insert and if it fails have a go at update" or "try update and if there is nothing to update add the row" strategies that used to be used. But did you know that you can (from Oracle 10 onwards) use merge to just add new records without updating existing records?

So, why did I want to do this? My customer has a table of hierarchy members, that is a table that looks quite a lot like the EMP table in the SCOTT schema, except that their table is a few million rows long; they also have a smaller table of new or updated members (let's call it NEW_EMP) that needs to be augmented with the parent records from the existing table (EMP) but only if there is not a replacement record for the parent in NEW_EMP.

We can simply build a merge statement and omit the WHEN MATCHED THEN UPDATE SET... clause and just use the WHEN NOT MATCHED THEN INSERT on its own.

MERGE INTO SCOTT.NEW_EMP NEW_EMP_1 USING
(SELECT DISTINCT EMP.EMPNO EMPNO,
EMP.ENAME ENAME      ,
EMP.JOB JOB          ,
EMP.MGR MGR          ,
EMP.HIREDATE HIREDATE,
EMP.SAL SAL          ,
EMP.COMM COMM        ,
EMP.DEPTNO DEPTNO
FROM SCOTT.EMP EMP,
SCOTT.NEW_EMP NEW_EMP
WHERE ( NEW_EMP.MGR                 = EMP.EMPNO )
) MERGE_SUBQUERY ON ( NEW_EMP_1.EMPNO = MERGE_SUBQUERY.EMPNO )
WHEN NOT MATCHED THEN
INSERT
(
NEW_EMP_1.EMPNO   ,
NEW_EMP_1.ENAME   ,
NEW_EMP_1.JOB     ,
NEW_EMP_1.MGR     ,
NEW_EMP_1.HIREDATE,
NEW_EMP_1.SAL     ,
NEW_EMP_1.COMM    ,
NEW_EMP_1.DEPTNO
)
VALUES
(
MERGE_SUBQUERY.EMPNO   ,
MERGE_SUBQUERY.ENAME   ,
MERGE_SUBQUERY.JOB     ,
MERGE_SUBQUERY.MGR     ,
MERGE_SUBQUERY.HIREDATE,
MERGE_SUBQUERY.SAL     ,
MERGE_SUBQUERY.COMM    ,
MERGE_SUBQUERY.DEPTNO
)
;

This construction is probably easier to understand than the traditional INSERT operation where we would need to self-outer join the NEW_EMP table to find the the candidate rows to insert.