Oracle:重复密钥更新 [英] Oracle: ON DUPLICATE KEY UPDATE
问题描述
我正在尝试实施从Michiel de Mare的此处找到的解决方案,以用一个更新多个记录(最好在语法意义上简单)查询.我尝试从中学习的示例代码如下:
I'm trying to implement a solution I found over here from 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:
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);
我正在使用Oracle(并且还不太熟悉SQL查询).
I'm using Oracle (and am not yet well versed in SQL queries).
基于一些动态内容,我将查询与上面的内容串联在一起.它的长度可能会有所不同,具体取决于我要更新的记录数量,但这是我生成的查询的示例:
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.
推荐答案
Oracle没有on duplicate key update
使用MERGE
代替:
Oracle doesn't have on duplicate key update
Use MERGE
instead:
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
这篇关于Oracle:重复密钥更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!