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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: