I needed some place to write this down. I don't use it often (or at all) and it has too many advantages for me to ignore it.
Interestically enough, the merge statement sometimes seems to have a huge performance advantage when needing to perform operations in bulk.
But this effect can be absent, when attempting merge statements on individual entries.
MERGE INTO target_table
USING source_table
ON search_condition
WHEN MATCHED THEN
UPDATE SET col1 = value1, col2 = value2,...
WHERE <update_condition>
[DELETE WHERE <delete_condition>]
WHEN NOT MATCHED THEN
INSERT (col1,col2,...)
values(value1,value2,...)
WHERE >insert_condition>;
USING source_table
ON search_condition
WHEN MATCHED THEN
UPDATE SET col1 = value1, col2 = value2,...
WHERE <update_condition>
[DELETE WHERE <delete_condition>]
WHEN NOT MATCHED THEN
INSERT (col1,col2,...)
values(value1,value2,...)
WHERE >insert_condition>;
MERGE INTO items gemc
USING (select orderid from orders where customer=5300) geme
ON (gemc.orderid = geme.orderid and gemc.articlenr = 120)
WHEN MATCHED THEN
UPDATE SET gemc.coupon = 'AFEY12',
gemc.amount = gemc.amount + 1
WHERE gemc.orderid = geme.orderid
AND gemc.articlenr = 120
WHEN NOT MATCHED THEN
INSERT(gemc.itemid, gemc.orderid, gemc.articleid, gemc.coupon, gemc.amount, gemc.creation)
VALUES(seq_items.nextval, geme.orderid, 120, 'AFEY12', 1, sysdate);
USING (select orderid from orders where customer=5300) geme
ON (gemc.orderid = geme.orderid and gemc.articlenr = 120)
WHEN MATCHED THEN
UPDATE SET gemc.coupon = 'AFEY12',
gemc.amount = gemc.amount + 1
WHERE gemc.orderid = geme.orderid
AND gemc.articlenr = 120
WHEN NOT MATCHED THEN
INSERT(gemc.itemid, gemc.orderid, gemc.articleid, gemc.coupon, gemc.amount, gemc.creation)
VALUES(seq_items.nextval, geme.orderid, 120, 'AFEY12', 1, sysdate);
References
- Wikipedia - Merge(SQL)
- https://en.wikipedia.org/wiki/Merge_(SQL)
- Oracle Tutorial - Oracle Merge
- https://www.oracletutorial.com/oracle-basics/oracle-merge/
No comments:
Post a Comment