更新在 Oracle 中插入重复的主键?

2022-01-09 00:00:00 merge insert upsert oracle

我有一个简单的 INSERT 查询，当主键重复时，我需要使用 UPDATE 代替.在 MySQL 中这似乎更容易，在 Oracle 中似乎我需要使用 MERGE.

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.

我能找到的所有 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.

MERGE 是唯一的方法还是有更好的解决方案?

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

我想使用一个触发器，它会在调用 INSERT 时自动执行 UPDATE 语句，但前提是主键是重复的.这样做有什么问题吗?我需要一些关于触发器的帮助，因为我在尝试理解它们并自己做时遇到了一些困难.

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 是标准 SQL 中的根据需要执行 INSERT 或 UPDATE"语句，因此在 Oracle SQL 中也可能如此.

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

(语法未验证.)

相关文章