Oracle:如何 UPSERT(更新或插入表?)

2021-12-01 00:00:00 merge sql upsert oracle

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;

相关文章