Use of multiple SET/WHERE clauses in the MERGE of 2 Oracle tables
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;
Software/Hardware used:
Software/Hardware used:
ASKED:
December 16, 2008 3:49 PM
UPDATED:
December 23, 2008 7:26 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
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