Oracle:如何 UPSERT(更新或插入表?)
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;
相关文章