Oracle Illustrated

Feb 21 2010   7:44AM GMT

Migrating from 9i to 11g – Merge enhancements

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: