merge - 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

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