Thursday 30 July 2020

The Merge Statement in SQL

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>;

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);