Migrating from 9i to 11g – Merge enhancements

More exciting features in 10g and 11g !!

With Oracle 10g INSERT / DELETE is now optional. In Oracle 11g new optional DELETE can be included in WHEN MATCHED clause.

OPTIONAL INSERT

MERGE
INTO emp1 tgt
USING emp src
ON (src.empno = tgt.empno)
WHEN MATCHED
THEN
UPDATE
SET tgt.still_employed = CASE WHEN src.JOB IS NOT NULL THEN ‘Y’ END
/

20 rows merged.

Elapsed: 00:00:00.01

Optional Update

MERGE
INTO emp1 tgt
USING emp src
ON (src.empno = tgt.empno)
WHEN NOT MATCHED
THEN
INSERT (tgt.empno, tgt.still_employed)
values (src.empno, ‘Y’)
/

40521 rows merged.

Elapsed: 00:00:03.78

Optional Delete

MERGE
INTO emp1 tgt
USING emp src
ON (src.empno = tgt.empno)
WHEN MATCHED THEN
UPDATE SET tgt.still_employed = CASE WHEN src.JOB IS NOT NULL THEN ‘Y’ END
DELETE WHERE tgt.still_employed = ‘N’
WHEN NOT MATCHED
THEN
INSERT (tgt.empno, tgt.still_employed)
values (src.empno, ‘Y’)
/

40541 rows merged.

Elapsed: 00:00:00.29