Oracle：关于重复密钥更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17254018/
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
Oracle: ON DUPLICATE KEY UPDATE
提问by John
I'm trying to implement a solution I found over herefrom Michiel de Mare to update multiple records with one (preferably-simple-in-a-syntax-sense) query. The example code that I am trying to learn from looks like this:
我想实现一个解决方案，我发现在这里从米希尔·德马雷一个（最好-简单的-A-语法意义上的）查询来更新多条记录。我试图学习的示例代码如下所示：
INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12) ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);
I'm using Oracle (and am not yet well versed in SQL queries).
我正在使用 Oracle（并且还不精通 SQL 查询）。
Based on some dynamic content, I have concatenated my query similar to the above. It can vary in length depending on how many records I am updating, but this is an example of a query that I generated:
基于一些动态内容，我已经连接了与上述类似的查询。它的长度可能因我要更新的记录数而异，但这是我生成的查询示例：
INSERT INTO my_table (question_id,ug) VALUES (30,0),(31,1) ON DUPLICATE KEY UPDATE ug=VALUES(ug)
The above query is getting this error:
上面的查询收到此错误：
Native message: ORA-00933: SQL command not properly ended
I am dealing with a content management system that has a function call that runs the queries; within this framework. I don't think it is pertinent, but I have never needed to put a ';' on the end of queries, however, I tried it with and without the semicolon.
我正在处理一个具有运行查询的函数调用的内容管理系统；在这个框架内。我不认为这是相关的，但我从来不需要放一个“;” 然而，在查询结束时，我尝试使用和不使用分号。
回答by a1ex07
Oracle doesn't have on duplicate key updateUse MERGEinstead:
Oracle 没有on duplicate key updateUse MERGE代替：
MERGE INTO my_table trg
USING (SELECT 30 as question_id,0 as ug FROM DUAL
UNION ALL
SELECT 31,1 FROM DUAL) src ON (src.question_id = trg.question_id)
WHEN NOT MATCHED THEN INSERT(question_id, ug) VALUES
(src.question_id, src.ug)
WHEN MATCHED THEN UPDATE
SET trg.ug = src.ug
