Upsert in Oracle SQL using Merge

Here is a handy bit of SQL for Oracle to allow you to do an Upsert on a row.

MERGE INTO UserDetails DEST
    USING(SELECT 1 AS ID FROM DUAL) src
    ON (DEST.userid = 'john.smith')          
WHEN MATCHED THEN 
    UPDATE SET DEST.GROUPID = '6'          
WHEN NOT MATCHED THEN 
    INSERT(DEST.USERID, DEST.GROUPID) VALUES ('john.smith', '6');

COMMIT;

I’ll quickly explain the code above.

1. We want to use the MERGE Oracle keyword on our destination table (prefixed DEST above)
2. The USING keyword requires a statement, in this instance we don’t care what the statement is, hence simple calling select 1 frm dual.
3. The ON brings back our actually query, in this case I want to see if I have user john.smith with Group ID of 5
4. The rest is pretty straight forward, if it’s MATCHED then do an update, if not the record isn’t there so do an insert.

View original on the Wayback Machine