By Mark Harrison


2008-10-26 01:24:06 8 Comments

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?

12 comments

@Mark Harrison 2008-10-26 01:24:17

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

@Tim Sylvester 2009-07-13 05:15:15

Apparently the "merge into" statement is not atomic. It can result in "ORA-0001: unique constraint" when used concurrently. The check for existence of a match and the insertion of a new record are not protected by a lock, so there is a race condition. To do this reliably, you need to catch this exception and either re-run the merge or do a simple update instead. In Oracle 10, you can use the "log errors" clause to make it continue with the rest of the rows when an error occurs and log the offending row to another table, rather than just stopping.

@Shekhar 2010-10-15 06:12:05

Hi, I tried to use same query pattern in my query but somehow my query is inserting duplicate rows. I am not able to find more information about DUAL table. Can anyone please tell me where can I get information of DUAL and also about merge syntax?

@YogoZuno 2010-11-19 04:56:16

@Shekhar Dual is a dummy table with a single row and columnn adp-gmbh.ch/ora/misc/dual.html

@Ricardo Villamil 2011-08-11 17:30:20

thanks! Small typo in your when matched then update, you should have the alias: update set m.b = ..

@Neo 2012-11-06 13:23:15

@TimSylvester - Oracle uses transactions, so guarantees the snapshot of data at the start of a transaction is consistent throughout the transaction save any changes made within it. Concurrent calls to the database uses the undo stack; so Oracle will manage the final state based on the order of when the concurrent transactions started/completed. So, you'll never have a race condition if a constraint check is done before the insert regardless of how many concurrent calls are made to same SQL code. Worst case, you may get lots of contention and Oracle will take much longer to reach a final state.

@user123444555621 2014-05-03 06:39:33

further reading on upserts using merge: idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_5.shtml

@Roland 2015-02-06 16:38:01

To make it 100% correct 'when matched' clause has to go before 'when not matched', cf. docs.oracle.com/cd/B19306_01/server.102/b14200/…

@Randy Magruder 2015-02-12 14:53:47

It's not clear to me what happens if you put an exception handler around a merge statement. If you intercept the ORA-00001 and say 'then null', does it insert the remainder of the rows that might NOT throw exceptions? Or does it bail quietly at the first exception? You're not suggesting you can wrap an exception INSIDE the merge statement around just the INSERT clause are you?

@dan b 2015-04-09 22:23:56

@RandyMagruder Is it the case that its 2015 and we still cannot do a upsert reliably in Oracle! Do you know of a concurrent safe solution?

@Randy Magruder 2015-08-27 16:05:12

Not reliably. I ended up with retry loops in the client code. :(

@Boneist 2016-01-14 17:07:24

@Roland From the same link that you posted: You can specify this [the merge_update_clause] clause by itself or with the merge_insert_clause. If you specify both, then they can be in either order. so the order of the matched/not matched clauses is arbitrary.

@Bechyƈák Petr 2017-10-09 15:08:19

Copy & paste example for upserting one table into another, with MERGE:

CREATE GLOBAL TEMPORARY TABLE t1
    (id VARCHAR2(5) ,
     value VARCHAR2(5),
     value2 VARCHAR2(5)
     )
  ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE t2
    (id VARCHAR2(5) ,
     value VARCHAR2(5),
     value2 VARCHAR2(5))
  ON COMMIT DELETE ROWS;
ALTER TABLE t2 ADD CONSTRAINT PK_LKP_MIGRATION_INFO PRIMARY KEY (id);

insert into t1 values ('a','1','1');
insert into t1 values ('b','4','5');
insert into t2 values ('b','2','2');
insert into t2 values ('c','3','3');


merge into t2
using t1
on (t1.id = t2.id) 
when matched then 
  update set t2.value = t1.value,
  t2.value2 = t1.value2
when not matched then
  insert (t2.id, t2.value, t2.value2)  
  values(t1.id, t1.value, t1.value2);

select * from t2

Result:

  1. b 4 5
  2. c 3 3
  3. a 1 1

@Hubbitus 2012-12-25 15:36:00

I'd like Grommit answer, except it require dupe values. I found solution where it may appear once: http://forums.devshed.com/showpost.php?p=1182653&postcount=2

MERGE INTO KBS.NUFUS_MUHTARLIK B
USING (
    SELECT '028-01' CILT, '25' SAYFA, '6' KUTUK, '46603404838' MERNIS_NO
    FROM DUAL
) E
ON (B.MERNIS_NO = E.MERNIS_NO)
WHEN MATCHED THEN
    UPDATE SET B.CILT = E.CILT, B.SAYFA = E.SAYFA, B.KUTUK = E.KUTUK
WHEN NOT MATCHED THEN
    INSERT (  CILT,   SAYFA,   KUTUK,   MERNIS_NO)
    VALUES (E.CILT, E.SAYFA, E.KUTUK, E.MERNIS_NO); 

@Matteo 2015-04-03 07:36:53

Did you mean INSERT (B.CILT, B.SAYFA, B.KUTUK, B.MERNIS_NO) VALUES (E.CILT, E.SAYFA, E.KUTUK, E.MERNIS_NO); ?

@Hubbitus 2015-04-03 10:03:39

Sure. Thanks. Fixed.

@Matteo 2015-04-03 14:20:09

Thankfully you edited your answer! :) my edit was sadly reject stackoverflow.com/review/suggested-edits/7555674

@Arturo Hernandez 2015-01-12 16:48:00

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.

@Eugene Beresovsky 2014-04-01 05:45:57

None of the answers given so far is safe in the face of concurrent accesses, as pointed out in Tim Sylvester's comment, and will raise exceptions in case of races. To fix that, the insert/update combo must be wrapped in some kind of loop statement, so that in case of an exception the whole thing is retried.

As an example, here's how Grommit's code can be wrapped in a loop to make it safe when run concurrently:

PROCEDURE MyProc (
 ...
) IS
BEGIN
 LOOP
  BEGIN
    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" );
    EXIT; -- success? -> exit loop
  EXCEPTION
    WHEN NO_DATA_FOUND THEN -- the entry was concurrently deleted
      NULL; -- exception? -> no op, i.e. continue looping
    WHEN DUP_VAL_ON_INDEX THEN -- an entry was concurrently inserted
      NULL; -- exception? -> no op, i.e. continue looping
  END;
 END LOOP;
END; 

N.B. In transaction mode SERIALIZABLE, which I don't recommend btw, you might run into ORA-08177: can't serialize access for this transaction exceptions instead.

@Sebien 2014-04-25 07:17:28

Excellent! Finally, a concurrent accesses safe answer. Any way to use such a construct from a client (eg. from a Java client)?

@Eugene Beresovsky 2014-04-25 08:15:44

You mean not having to call a stored proc? Well, in that case you could also just catch the specific Java exceptions and retry in a Java loop. It's a hell of a lot more convenient in Java than Oracle's SQL.

@Sebien 2014-04-25 13:26:01

I'm sorry: I was not specific enough. But you understood the right way. I resigned to do like you just said. But I'm not 100% satisfied because it generates more SQL queries, more client/server roundtrips. It is not a good solution performance-wise. But my goal is to let the Java developers of my project use my method to upsert in any table (I cannot create one PLSQL stored procedure per table, or one procedure per upsert type).

@Eugene Beresovsky 2014-04-27 23:59:26

@Sebien I agree, it'd be nicer to have it encapsulated in the SQL realm, and I think you can do it. I'm just not volunteering to figure it out for you... :) Plus, in reality these exceptions will probably occur less than once in a blue moon, so you shouldn't see an impact on performance in 99.9% of cases. Except when doing load testing of course...

@test1 2014-01-23 14:02:31

  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;

@r4bitt 2011-03-15 03:34:15

Try this,

insert into b_building_property (
  select
    'AREA_IN_COMMON_USE_DOUBLE','Area in Common Use','DOUBLE', null, 9000, 9
  from dual
)
minus
(
  select * from b_building_property where id = 9
)
;

@AnthonyVO 2011-11-25 23:19:47

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.

@Arturo Hernandez 2015-01-12 16:56:18

sql%notfound is my personal preference

@MyDeveloperDay 2010-04-22 16:00:39

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.

@Synesso 2011-05-18 06:27:23

I've come back here to check out this pattern again. It fails silently when concurrent inserts are attempted. One insert takes effect, the second merge neither inserts or updates. However, the faster approach of doing two separate statements is safe.

@Hajo Thelen 2012-08-20 16:44:41

oralcle newbies like me may ask what is this dual table see this : stackoverflow.com/q/73751/808698

@Nicolas Barbulesco 2013-11-15 16:17:13

Too bad that with this pattern we need to write twice the data (John, Smith...). In this case, I win nothing using MERGE, and I prefer using much simpler DELETE then INSERT.

@Anon 2010-02-10 01:24:03

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;

@Tony Andrews 2011-08-24 10:36:45

-1 Typical Don Burleson [email protected] I'm afraid - this is an insert into one table or another, there is no "upsert" here!

@Brian Schmitt 2008-10-28 14:37:32

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;

@Synesso 2011-01-31 01:22:48

Your provided solution does not work for me. Does %rowcount only work with explicit cursors?

@Adriano Varoli Piazza 2011-08-01 14:40:14

What if the update returned 0 rows modified because the record was already there and the values were the same?

@Tony Andrews 2011-08-24 10:39:42

@Adriano: sql%rowcount will still return > 0 if the WHERE clause matches any rows, even if the update doesn't actually change any data on those rows.

@Falcon 2011-10-17 10:19:26

@Tony Andrews - is this solution thread safe?

@Patrik Beck 2013-09-27 15:57:51

Doesn't work: PLS-00207: identifier 'COUNT', applied to implicit cursor SQL, is not a legal cursor attribute

@Tony Andrews 2008-10-27 11:12:13

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;   

@chotchki 2011-09-28 17:48:56

This only works if you never delete from table in question.

@Tony Andrews 2011-09-29 08:51:26

@chotchki: really? An explanation would be helpful.

@chotchki 2011-09-29 22:50:01

The issue is that you have a window in between the insert and the update where another process could successfully fire a delete. I did however use this pattern on a table that never has deletes fired against it.

@Tony Andrews 2011-09-30 11:22:36

OK, I agree. Don't know why it wasn't obvious to me.

@yohannc 2014-10-28 08:45:09

I disagree with Chotchki. "Lock Duration : All locks acquired by statements within a transaction are held for the duration of the transaction, preventing destructive interference including dirty reads, lost updates, and destructive DDL operations from concurrent transactions." Souce : link

@Tony Andrews 2014-10-28 09:49:54

@yohannc: I think the point is that we haven't acquired any locks just by trying and failing to insert a row.

Related Questions

Sponsored Content

7 Answered Questions

41 Answered Questions

[SOLVED] How to merge two dictionaries in a single expression?

21 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2075623 View
  • 1047 Score
  • 21 Answer
  • Tags:   sql oracle

37 Answered Questions

16 Answered Questions

[SOLVED] Insert, on duplicate update in PostgreSQL?

27 Answered Questions

17 Answered Questions

[SOLVED] SQLite - UPSERT *not* INSERT or REPLACE

  • 2009-01-07 01:47:23
  • Mike Trader
  • 271225 View
  • 501 Score
  • 17 Answer
  • Tags:   sql sqlite upsert

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

21 Answered Questions

[SOLVED] Solutions for INSERT OR UPDATE on SQL Server

6 Answered Questions

[SOLVED] How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

Sponsored Content