Oracle: how to UPSERT (update or insert into a tab

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

A note regarding the two solutions that suggest:

1) Insert, if exception then update,

or

2) Update, if sql%rowcount = 0 then insert

The question of whether to insert or update first is also application dependent. Are you expecting more inserts or more updates? The one that is most likely to succeed should go first.

If you pick the wrong one you will get a bunch of unnecessary index reads. Not a huge deal but still something to consider.

Answer 2

1. insert if not exists
2. update:

    
INSERT INTO mytable (id1, t1) 
  SELECT 11, 'x1' FROM DUAL 
  WHERE NOT EXISTS (SELECT id1 FROM mytble WHERE id1 = 11); 

UPDATE mytable SET t1 = 'x1' WHERE id1 = 11;

Answer 3

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 4

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 5

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

Answer 6

I've been using the first code sample for years. Notice notfound rather than count.

UPDATE tablename SET val1 = in_val1, val2 = in_val2
    WHERE val3 = in_val3;
IF ( sql%notfound ) THEN
    INSERT INTO tablename
        VALUES (in_val1, in_val2, in_val3);
END IF;

The code below is the possibly new and improved code

MERGE INTO tablename USING dual ON ( val3 = in_val3 )
WHEN MATCHED THEN UPDATE SET val1 = in_val1, val2 = in_val2
WHEN NOT MATCHED THEN INSERT 
    VALUES (in_val1, in_val2, in_val3)

In the first example the update does an index lookup. It has to, in order to update the right row. Oracle opens an implicit cursor, and we use it to wrap a corresponding insert so we know that the insert will only happen when the key does not exist. But the insert is an independent command and it has to do a second lookup. I don't know the inner workings of the merge command but since the command is a single unit, Oracle could have execute the correct insert or update with a single index lookup.

I think merge is better when you do have some processing to be done that means taking data from some tables and updating a table, possibly inserting or deleting rows. But for the single row case, you may consider the first case since the syntax is more common.