UPDATE on INSERT duplicate primary key in Oracle?

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 wi开发者_开发技巧th triggers though as I'm having some difficulty trying to understand them and doing my own.

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

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

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.