Coder Perfect

How do you UPSERT (update or insert data into a table) in Oracle?


Depending on whether the table already includes a row that matches the data, the UPSERT action either updates or inserts a record:

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

What’s the best approach to handle this because Oracle doesn’t have a particular UPSERT statement?

Asked by Mark Harrison

Solution #1

The MERGE command joins two tables together. We may utilize this command by using DUAL. It’s worth noting that this isn’t protected from concurrent access.

create or replace
procedure ups(xa number)
    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

Answered by Mark Harrison

Solution #2

The PL/SQL example above was wonderful since I wanted to do something similar on the client side…so here’s the SQL I used to deliver a similar statement directly from some C# code.

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 appears to be slower than performing the update and then checking to see if the number of rows affected is zero before inserting.

Answered by MyDeveloperDay

Solution #3

An alternative to MERGE (the “traditional” method):

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

Answered by Tony Andrews

Solution #4

Another alternative without the exception check:

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

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

Answered by Brian Schmitt

Solution #5

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;

Answered by test1

Post is based on