Oracle: how to UPSERT (update or insert into a table?)

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 1

The MERGE statement merges data between two tables. Using DUAL allows us to use this command. Note that this is not protected against concurrent access.

create or replace procedure ups(xa number) as begin     merge into mergetest m using dual on (a = xa)          when not matched then insert (a,b) values (xa,1)              when matched then update set b = b+1; end ups; / drop table mergetest; create table mergetest(a number, b number); call ups(10); call ups(10); call ups(20); select * from mergetest;  A                      B ---------------------- ---------------------- 10                     2 20                     1