into merge 主键重复,在INSERT中重复更新主键在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 with 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.)