在INSERT中重复更新主键在Oracle中? [英] UPDATE on INSERT duplicate primary key in 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.

是合并的唯一方法，还是有更好的解决方案?

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

(语法未经验证.)

这篇关于在INSERT中重复更新主键在Oracle中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！