oracle sql:更新(如果存在),否则插入 [英] oracle sql: update if exists else insert
问题描述
Possible Duplicate:
Oracle: how to UPSERT (update or insert into a table?)
我有一个表,如果该记录已经存在,则必须在其中进行修改,否则必须插入新记录.
Oracle sql不接受IF EXISTS
,否则我会做一个if - update - else - insert
查询.我看过MERGE
,但是它仅适用于多个表.我该怎么办?
I have a table in which a record has to be modified if it already exists else a new record has to be inserted.
Oracle sql doesnt accept IF EXISTS
, otherwise I would have done an if - update - else - insert
query. I've looked at MERGE
but it only works for multiple tables. What do i do?
推荐答案
MERGE不需要多个表",但确实需要查询作为源.这样的事情应该起作用:
MERGE doesn't need "multiple tables", but it does need a query as the source. Something like this should work:
MERGE INTO mytable d
USING (SELECT 1 id, 'x' name from dual) s
ON (d.id = s.id)
WHEN MATCHED THEN UPDATE SET d.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);
或者,您也可以在PL/SQL中执行此操作:
Alternatively you can do this in PL/SQL:
BEGIN
INSERT INTO mytable (id, name) VALUES (1, 'x');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
UPDATE mytable
SET name = 'x'
WHERE id = 1;
END;
这篇关于oracle sql:更新(如果存在),否则插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!