Oracle:如何 UPSERT(更新或插入表?) [英] Oracle: how to UPSERT (update or insert into a table?)

问题描述

UPSERT 操作要么更新要么在表中插入一行，具体取决于该表是否已有与数据匹配的行:

The UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data:

if table t has a row exists that has key X:
    update t set mystuff... where mykey=X
else
    insert into t mystuff...

由于 Oracle 没有特定的 UPSERT 语句，那么执行此操作的最佳方法是什么?

Since Oracle doesn't have a specific UPSERT statement, what's the best way to do this?

推荐答案

MERGE(老式方法")的替代方案:

An alternative to MERGE (the "old fashioned way"):

begin
   insert into t (mykey, mystuff) 
      values ('X', 123);
exception
   when dup_val_on_index then
      update t 
      set    mystuff = 123 
      where  mykey = 'X';
end;   

这篇关于Oracle:如何 UPSERT(更新或插入表?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！