44

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

Hi,

I have a table in which a record has to be modified if it already exists else a new record has to be inserted. Oracle sql doesnt accept IF EXISTS, otherwise I would have done an if - update - else - insert query. I've looked at MERGE but it only works for multiple tables. What do i do?

Community
  • 1
  • 1
Aks
  • 555
  • 1
  • 6
  • 8

6 Answers6

84

MERGE doesn't need "multiple tables", but it does need a query as the source. Something like this should work:

MERGE INTO mytable d
USING (SELECT 1 id, 'x' name from dual) s
ON (d.id = s.id)
WHEN MATCHED THEN UPDATE SET d.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

Alternatively you can do this in PL/SQL:

BEGIN
  INSERT INTO mytable (id, name) VALUES (1, 'x');
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UPDATE mytable
    SET    name = 'x'
    WHERE id = 1;
END;
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 2
    +1 I don't know about the `MERGE` instruction, but as for the exception `DUP_VAL_ON_INDEX` handling, that is definitely a nice solution, knowing that Oracle exception handling is regularly used for such behaviour! =) – Will Marcouiller Oct 25 '10 at 14:14
  • 2
    +1; it should be noted that the alternative solution is generally a lot less efficient. – DCookie Oct 25 '10 at 15:58
  • How to do it when ID is unknown? In example you searching row by name and want to change surname... – Dumbo Sep 20 '18 at 10:37
  • @Dumbo You should have a unicity constraint on the column right? – Yassin Hajaj Jul 31 '20 at 09:39
  • For anyone coming to this thread in the future, note that if you actually want to see the results in your OracleSQL Database after running `MERGE INTO` statement, make sure to commit the transaction by running `COMMIT;` – Vincent Dec 08 '20 at 21:16
13
merge into MY_TABLE tgt
using (select [expressions]
         from dual ) src
   on (src.key_condition = tgt.key_condition)
when matched then 
     update tgt
        set tgt.column1 = src.column1 [,...]
when not matched then 
     insert into tgt
        ([list of columns])
     values
        (src.column1 [,...]);
Adam Musch
  • 13,286
  • 2
  • 28
  • 32
10

You could use the SQL%ROWCOUNT Oracle variable:

UPDATE table1
  SET field2 = value2, 
      field3 = value3 
WHERE field1 = value1; 

IF (SQL%ROWCOUNT = 0) THEN 

  INSERT INTO table (field1, field2, field3)
  VALUES (value1, value2, value3);

END IF;

It would be easier just to determine if your primary key (i.e. field1) has a value and then perform an insert or update accordingly. That is, if you use said values as parameters for a stored procedure.

Anders
  • 8,307
  • 9
  • 56
  • 88
  • 12
    If you have multiple sessions writing simultaneously, you might run into the situation that the `update` touches zero rows so you assume there is no row and need to do an `insert`, but in the mean-time someone has done an `insert` so your `insert` fails with a unique constraint violation. That's why it's important to do `insert` (and catch unique constraint violations) then `update`, not the other way around. – Adrian Smith Oct 25 '10 at 14:12
  • Worst logic i have ever seen – Vimal Bhaskar Jun 15 '18 at 08:58
2

The way I always do it (assuming the data is never to be deleted, only inserted) is to

  • Firstly do an insert, if this fails with a unique constraint violation then you know the row is there,
  • Then do an update

Unfortunately many frameworks such as Hibernate treat all database errors (e.g. unique constraint violation) as unrecoverable conditions, so it isn't always easy. (In Hibernate the solution is to open a new session/transaction just to execute this one insert command.)

You can't just do a select count(*) .. where .. as even if that returns zero, and therefore you choose to do an insert, between the time you do the select and the insert someone else might have inserted the row and therefore your insert will fail.

Adrian Smith
  • 17,236
  • 11
  • 71
  • 93
  • Even with the insert only constraint, using two transactions could lead to uniqueness exceptions if there are multiple updaters writing to the table. – David Mann Feb 21 '13 at 19:06
  • David Mann, I'm sorry I didn't understand; please clarify. – Adrian Smith Feb 24 '13 at 09:46
  • Hi Adrian, I'm just thinking about a case where the UPDATE statement is only valid if the data has not changed since the attempted INSERT, and there are multiple processes performing the inserts and updates. Unless all inserts and updates to the table are synchronized, sometimes the UPDATE may succeed and sometimes it may fail depending on how the processes interleave. It seemed like such a corner case to me until I was required to perform UPDATEs that are only valid if the state of the data since the last read is still the same. – David Mann Feb 25 '13 at 15:13
1

HC-way :)

DECLARE
  rt_mytable mytable%ROWTYPE;
  CURSOR update_mytable_cursor(p_rt_mytable IN mytable%ROWTYPE) IS
  SELECT *
  FROM   mytable
  WHERE  ID = p_rt_mytable.ID
  FOR UPDATE;
BEGIN
  rt_mytable.ID   := 1;
  rt_mytable.NAME := 'x';
  INSERT INTO mytable VALUES (rt_mytable);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
  <<update_mytable>>
  FOR i IN update_mytable_cursor(rt_mytable) LOOP
    UPDATE mytable SET    
      NAME = p_rt_mytable.NAME
    WHERE CURRENT OF update_mytable_cursor;
  END LOOP update_mytable;
END;
Jokke Heikkilä
  • 918
  • 1
  • 8
  • 17
-1

Please refer to this question if you want to use UPSERT/MERGE command in Oracle. Otherwise, just resolve your issue on the client side by doing a count(1) first and then deciding whether to insert or update.

Community
  • 1
  • 1
Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292