SQL MERGE - 当不匹配 INSERT 的异常时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3599698/
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
MERGE - When not matched INSERT's exception
提问by mcha
i have a PL/SQL procedure using MERGE :
我有一个使用 MERGE 的 PL/SQL 过程：
MERGE INTO
table_dest d
USING
(SELECT * FROM my_Table) s
ON
(s.id = d.id)
when matched then UPDATE set d.col1 = s.col1
when not matched then INSERT (id, col1) values (s.id, s.col1);
now lets say the query sreturns mutiple rows with same id wich will returns an ORA-00001: unique constrain error
现在可以说查询s返回具有相同 ID 的多行，这将返回ORA-00001：唯一约束错误
what i want to do is to send the duplicated columns to another table my_Table_recyledbinto get a successful INSERT, can i use EXCEPTION WHEN DUP_VAL_ON_INDEX? if yes how to use it with the MERGE statement?
我想要做的是将重复的列发送到另一个表my_Table_recyledbin以获得成功的插入，我可以使用EXCEPTION WHEN DUP_VAL_ON_INDEX吗？如果是，如何将它与 MERGE 语句一起使用？
Thanks in advance
提前致谢
回答by Tom
Why not handle the archiving of duplicate rows to the recycle bin table in a separate statement?
为什么不在单独的语句中处理将重复行归档到回收站表？
Firstly, do your merge (aggregating the duplicate rows to avoid the unique constraint error). I've assumed a MAXaggregate function on col1, but you can use whatever suits your needs -- you have not specified how to decide which row to use when there are duplicates.
首先，进行合并（聚合重复的行以避免唯一约束错误）。我MAX在 col1 上假设了一个聚合函数，但是您可以使用任何适合您需要的函数——您没有指定如何在有重复项时决定使用哪一行。
MERGE INTO
table_dest d
USING
(SELECT a.id, MAX(a.col1) as col1
FROM my_Table a
GROUP BY a.id) s
ON
(s.id = d.id)
WHEN MATCHED THEN UPDATE SET d.col1 = s.col1
WHEN NOT MATCHED THEN INSERT (id, col1) VALUES (s.id, s.col1);
Then, deal with the duplicate rows. I'm assuming that your recycle bin table does allow duplicate ids to be inserted:
然后，处理重复的行。我假设您的回收站表确实允许插入重复的 ID：
INSERT INTO my_Table_recyledbin r (id, col1)
SELECT s.id, s.col1
FROM my_Table s
WHERE EXISTS (SELECT 1
FROM my_Table t
WHERE t.id = s.id
AND t.ROWID != s.ROWID)
Hopefully, that should fulfil your needs.
希望这能满足您的需求。
回答by Dave Costa
Can't you just use an error-logging clause? I.E., add this line at the end of your MERGE statement:
你不能只使用错误记录条款吗？IE，在 MERGE 语句的末尾添加这一行：
LOG ERRORS INTO my_Table_recycledbin
