在 Oracle 中插入重复主键的更新？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4589647/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UPDATE on INSERT duplicate primary key in Oracle?
提问by rfgamaral
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.
我有一个简单的 INSERT 查询，当主键重复时，我需要使用 UPDATE 代替。在 MySQL 中这似乎更容易，在 Oracle 中似乎我需要使用 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?
MERGE 是唯一的方法还是有更好的解决方案？
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:
基本上就是这样，主键是前 2 个值，因此更新将如下所示：
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.
我想使用一个触发器，它会在调用 INSERT 时自动执行 UPDATE 语句，但前提是主键是重复的。这样做有什么问题吗？我需要一些触发器方面的帮助，因为我在尝试理解它们并自己做时遇到了一些困难。
回答by Jonathan Leffler
MERGE is the 'do INSERT or UPDATE as appropriate' statement in Standard SQL, and probably therefore in Oracle SQL too.
MERGE 是标准 SQL 中的“适当地执行插入或更新”语句，因此在 Oracle SQL 中也可能如此。
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.)
（语法未验证。）
回答by sjngm
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
- 执行 INSERT 并捕获 DUP_VAL_ON_INDEX 然后执行 UPDATE
- 首先执行 UPDATE 并且如果 SQL%Rows = 0 执行 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).
你不能在一个表上写一个触发器来在同一个表上执行另一个操作。这会导致 Oracle 错误（变异表）。
回答by Chris Gessler
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...
我是一个 T-SQL 人，但在这种情况下触发器不是一个好的解决方案。大多数触发器都不是好的解决方案。在 T-SQL 中，我会简单地执行 IF EXISTS (SELECT * FROM dbo.Table WHERE ...) 但在 Oracle 中，您必须选择计数...
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 ：
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.
就像我说的那样，我是一个 T-SQL 人，但这里的基本思想是“加入”movie_rating 表以对抗自身。如果使用“如果存在”示例没有性能影响，我会使用它来提高可读性。
