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.

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

Answer 2

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;

Answer 3

Another alternative without the exception check:

UPDATE tablename
    SET val1 = in_val1,
        val2 = in_val2
    WHERE val3 = in_val3;

IF ( sql%rowcount = 0 )
    THEN
    INSERT INTO tablename
        VALUES (in_val1, in_val2, in_val3);
END IF;

Answer 4

Only option 2 will work in case of concurrent upserts.

In option 1 and option 3, both sessions could think that the row doesn't exist yet, both try to insert a new row => one of them will get a dup_val_on_index.

Answer 5

From http://www.praetoriate.com/oracle_tips_upserts.htm:

"In Oracle9i, an UPSERT can accomplish this task in a single statement:"

INSERT
FIRST WHEN
   credit_limit >=100000
THEN INTO
   rich_customers
VALUES(cust_id,cust_credit_limit)
   INTO customers
ELSE
   INTO customers SELECT * FROM new_customers;

Answer 6

The dual example above which is in PL/SQL was great becuase I wanted to do something similar, but I wanted it client side...so here is the SQL I used to send a similar statement direct from some C#

MERGE INTO Employee USING dual ON ( "id"=2097153 ) WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john" WHEN NOT MATCHED THEN INSERT ("id","last","name") VALUES ( 2097153,"smith", "john" )

However from a C# perspective this provide to be slower than doing the update and seeing if the rows affected was 0 and doing the insert if it was.