UPDATE on INSERT duplicate primary key in Oracle?

Question

I have a simple INSERT query where I need to use UPDATE instead when the primary key is a duplicate. In MySQL this seems easier, in Oracle it seems I need to use MERGE.

All examples I could find of MERGE had some sort of "source" and "target" tables, in my case, the source and target is the same table. I was not able to make sense of the examples to create my own query.

Is MERGE the only way or maybe there's a better solution?

INSERT INTO movie_ratings
VALUES (1, 3, 5)

It's basically this and the primary key is the first 2 values, so an update would be like this:

UPDATE movie_ratings
SET rating = 8
WHERE mid = 1 AND aid = 3

I thought of using a trigger that would automatically execute the UPDATE statement when the INSERT was called but only if the primary key is a duplicate. Is there any problem doing it this way? I need some help with triggers though as I'm having some difficulty trying to understand them and doing my own.

In an example like this, it would be sensible to use the (optional) list of column names in the INSERT to give us enough of the table structure. Maybe if you showed what is done in MySQL, people might see what you're after - or maybe that's my curiosity/laziness. — Jonathan Leffler, Jan 04 '11 at 00:34
The table structure is implict, it's mid(int), aid(int), rating(int). — rfgamaral, Jan 04 '11 at 00:52

score 11 · Accepted Answer · edited Mar 25 '11 at 10:48

11

MERGE is the 'do INSERT or UPDATE as appropriate' statement in Standard SQL, and probably therefore in Oracle SQL too.

Yes, you need a 'table' to merge from, but you can almost certainly create that table on the fly:

 MERGE INTO Movie_Ratings M
       USING (SELECT 1 AS mid, 3 AS aid, 8 AS rating FROM dual) N
          ON (M.mid = N.mid AND M.aid = N.aid)
       WHEN     MATCHED THEN UPDATE SET M.rating = N.rating
       WHEN NOT MATCHED THEN INSERT(  mid,   aid,   rating)
                             VALUES(N.mid, N.aid, N.rating);

(Syntax not verified.)

edited Mar 25 '11 at 10:48

Stompchicken

15,833
1
33
38

answered Jan 04 '11 at 00:46

Jonathan Leffler

730,956
141
904
1,278

@jonathan-leffler I edited the answer to remove the 'AS' from 'USING ... AS N' since it causes an error for me on Oracle 11.1 – Stompchicken Mar 25 '11 at 10:51
Oracle allows **multi-table** inserts/updates with INSERT/UPDATE statement, but not with the MERGE statement. Talking about unfinished software... – Davor Josipovic Jul 11 '14 at 12:03
This is not working for me on Oracle 11g... anyone else has the problem?? The error I get: "Columns referenced in the ON Clause cannot be updated" as long as I have more than one condition in the "on" clause. I tried with / without primary keys, etc... – Peter Jun 12 '17 at 13:30

sjngm · Answer 2 · 2011-01-04T00:52:23.873

2

A typical way of doing this is

performing the INSERT and catch a DUP_VAL_ON_INDEX and then perform an UPDATE instead
performing the UPDATE first and if SQL%Rows = 0 perform an INSERT

You can't write a trigger on a table that does another operation on the same table. That's causing an Oracle error (mutating tables).

edited Jan 04 '11 at 00:52

answered Jan 04 '11 at 00:31

sjngm

12,423
14
84
114

Didn't know that, thanks for the clarification. But that should have been a comment as it doesn't necessarily answer my question. – rfgamaral Jan 04 '11 at 00:34
@Nazgulled: added some thoughts about the MERGE (I think it qualifies as an answer now) – sjngm Jan 04 '11 at 00:41
@Nazgulled: I removed my MERGE-stuff as Jonathan Leffler's suggestion might actually work that way. I had a different solution in mind. – sjngm Jan 04 '11 at 00:53

score 0 · Answer 3 · answered Jan 04 '11 at 00:53

I'm a T-SQL guy but a trigger in this case is not a good solution. Most triggers are not good solutions. In T-SQL, I would simply perform an IF EXISTS (SELECT * FROM dbo.Table WHERE ...) but in Oracle, you have to select the count...

DECLARE 
  cnt NUMBER;
BEGIN
  SELECT COUNT(*)
   INTO cnt
    FROM mytable
  WHERE id = 12345;

  IF( cnt = 0 )
  THEN
    ...
  ELSE
    ...
  END IF;
END;

It would appear that MERGE is what you need in this case:

MERGE INTO movie_ratings mr
USING (
  SELECT rating, mid, aid
  WHERE mid = 1 AND aid = 3) mri
ON (mr.movie_ratings_id = mri.movie_ratings_id)

WHEN MATCHED THEN
  UPDATE SET mr.rating = 8 WHERE mr.mid = 1 AND mr.aid = 3

WHEN NOT MATCHED THEN
  INSERT (mr.rating, mr.mid, mr.aid)
  VALUES (1, 3, 8)

Like I said, I'm a T-SQL guy but the basic idea here is to "join" the movie_rating table against itself. If there's no performance hit on using the "if exists" example, I'd use it for readability.

UPDATE on INSERT duplicate primary key in Oracle?

3 Answers3

Linked