How to UPSERT (update or insert into a table?)

ghz 1years ago ⋅ 9386 views

Question

The UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data:

if table t has a row exists that has key X:
    update t set mystuff... where mykey=X
else
    insert into t mystuff...

Since Oracle doesn't have a specific UPSERT statement, what's the best way to do this?

Answer

An alternative to MERGE (the "old fashioned way"):

begin
   insert into t (mykey, mystuff) 
      values ('X', 123);
exception
   when dup_val_on_index then
      update t 
      set    mystuff = 123 
      where  mykey = 'X';
end;
#oracle