Use of multiple SET/WHERE clauses in the MERGE of 2 Oracle tables

15 pts.
Tags:
MERGE statement
Oracle Table
PL/SQL
Currently am able to update 1 Oracle table from another using MERGE along with WHEN MATCHED THEN UPDATE logic. Under the UPDATE command I am using a SET/WHERE to update a specific column based on certain criteria. I want to add another SET/WHERE condition to this same update which will result in an UPDATE of a diff column if a diff condition (WHERE) is met. Below I have pasted an example of the code which is working, as well as the modified code which I am unable to run successfully. It appears 2 SET/WHERE conditions under the same UPDATE won't work ... or I have not syntactically coded this correctly. Thanks for any help you can provide: == code below is working just fine == MERGE INTO mbs_daterange md USING ( SELECT * FROM cmo_keyvals ) ck ON (md.seqno = ck.seqno AND md.item = ck.item) WHEN MATCHED THEN UPDATE SET md.end_range = ck.end_range WHERE ck.end_range > md.end_range WHEN NOT MATCHED THEN INSERT (md.seqno, md.item, md.begin_range, md.end_range, md.location) VALUES (ck.seqno, ck.item, ck.end_range, ck.end_range, NULL); commit; == modified code below does not work == MERGE INTO mbs_daterange md USING ( SELECT * FROM cmo_keyvals ) ck ON (md.seqno = ck.seqno AND md.item = ck.item) WHEN MATCHED THEN UPDATE SET md.end_range = ck.end_range WHERE ck.end_range > md.end_range SET md.begin_range = ck.end_range WHERE ck.end_range < md.begin_range WHEN NOT MATCHED THEN INSERT (md.seqno, md.item, md.begin_range, md.end_range, md.location) VALUES (ck.seqno, ck.item, ck.end_range, ck.end_range, NULL); commit;

Answer Wiki

Thanks. We'll let you know when a new response is added.

Only one SET/WHERE clause is allowed in an update statement.

You could use the decode and sign functions to do those updates. Something like this:

<pre>MERGE INTO mbs_daterange md
USING ( SELECT * FROM cmo_keyvals ) ck
ON (md.seqno = ck.seqno AND md.item = ck.item)
WHEN MATCHED THEN
UPDATE
SET md.end_range = DECODE(SIGN(ck.end_range-md.end_range),1,ck.end_range,md.end_range),
md.begin_range = DECODE(SIGN(ck.end_range-md.begin_range),-1,ck.end_range,md.begin_range)
WHERE ck.end_range > md.end_range or ck.end_range < md.begin_range
WHEN NOT MATCHED THEN
INSERT (md.seqno, md.item, md.begin_range, md.end_range, md.location)
VALUES (ck.seqno, ck.item, ck.end_range, ck.end_range, NULL);</pre>

Note that this will always update both columns (md.end_range and md.begin_range), but one of them will be updated with its same value.

Discuss This Question: 1  Reply

 
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 members answer or reply to this question.

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
  • Ejpeth
    Carlos, Use of DECODE function was a great alternative that solves my problem without having to drop the MERGE approach and start from scratch. Thank you, excellent advice and much appreciated ! EP
    15 pointsBadges:
    report

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:

To follow this tag...

There was an error processing your information. Please try again later.

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

Thanks! We'll email you when relevant content is added and updated.

Following