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.

Answer 1

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).

Answer 2

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.)

Answer 3

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.