愿所有的美好和期待都能如约而至

oracle sql:如果存在则更新否则插入

发布时间:  来源:互联网  作者:匿名  标签:error oracle sql: update if exists else insert exception IT资讯 oracle sql 技术社区 教程  热度:37.5℃

本文介绍了oracle sql:如果存在则更新否则插入的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

可能的重复:
Oracle:如何 UPSERT(更新或插入表?)

我有一个表,如果记录已经存在,则必须修改其中的记录,否则必须插入新记录.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:如果存在则更新否则插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,

勇敢去编程!

勇敢的热爱编程,未来的你一定会大放异彩,未来的生活一定会因编程更好!

TOP