如何UPSERT(更新或插入到表中?)

UPSERT操作在表中更新或插入一行，这取决于表中是否已经有与数据匹配的行:

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语句，那么最好的方法是什么呢?

499618 次浏览
小开
MERGE语句合并两个表之间的数据。使用双 允许我们使用此命令。注意这是不受并发访问保护的 
create or replace
procedure ups(xa number)
as
begin
merge into mergetest m using dual on (a = xa)
when not matched then insert (a,b) values (xa,1)
when matched then update set b = b+1;
end ups;
/
drop table mergetest;
create table mergetest(a number, b number);
call ups(10);
call ups(10);
call ups(20);
select * from mergetest;


A                      B
---------------------- ----------------------
10                     2
20                     1
小开
最佳答案

MERGE(“老式的方式”)的替代方案:

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;
小开

没有异常检查的另一种方法:

UPDATE tablename
SET val1 = in_val1,
val2 = in_val2
WHERE val3 = in_val3;


IF ( sql%rowcount = 0 )
THEN
INSERT INTO tablename
VALUES (in_val1, in_val2, in_val3);
END IF;
小开

从http://www.praetoriate.com/oracle_tips_upserts.htm:

在Oracle9i中，UPSERT可以在一条语句中完成这个任务:

INSERT
FIRST WHEN
credit_limit >=100000
THEN INTO
rich_customers
VALUES(cust_id,cust_credit_limit)
INTO customers
ELSE
INTO customers SELECT * FROM new_customers;
小开

上面在PL/SQL中的双重示例很棒，因为我想做一些类似的事情，但我想在客户端……所以这里是SQL我用来发送一个类似的语句直接从一些c#

MERGE INTO Employee USING dual ON ( "id"=2097153 )
WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
WHEN NOT MATCHED THEN INSERT ("id","last","name")
VALUES ( 2097153,"smith", "john" )

然而，从c#的角度来看，这比执行更新和查看受影响的行是否为0以及如果为0则执行插入要慢。

小开

试试这个,

insert into b_building_property (
select
'AREA_IN_COMMON_USE_DOUBLE','Area in Common Use','DOUBLE', null, 9000, 9
from dual
)
minus
(
select * from b_building_property where id = 9
)
;
小开

关于两种解决方案的说明如下:

1)插入，如果异常则更新，

或

2)更新，如果sql%rowcount = 0，则插入

先插入还是先更新的问题也取决于应用程序。您是否期待更多的插入或更新?最有可能成功的应该先走。

如果你选错了，你会得到一堆不必要的索引读取。不是什么大问题，但仍然值得考虑。

小开

我想要Grommit的答案，除了它需要欺骗的值。我发现解决方案，它可能出现一次:http://forums.devshed.com/showpost.php?p=1182653&postcount=2

MERGE INTO KBS.NUFUS_MUHTARLIK B
USING (
SELECT '028-01' CILT, '25' SAYFA, '6' KUTUK, '46603404838' MERNIS_NO
FROM DUAL
) E
ON (B.MERNIS_NO = E.MERNIS_NO)
WHEN MATCHED THEN
UPDATE SET B.CILT = E.CILT, B.SAYFA = E.SAYFA, B.KUTUK = E.KUTUK
WHEN NOT MATCHED THEN
INSERT (  CILT,   SAYFA,   KUTUK,   MERNIS_NO)
VALUES (E.CILT, E.SAYFA, E.KUTUK, E.MERNIS_NO);
小开
  1. 如果不存在，请插入
  2. 更新:
INSERT INTO mytable (id1, t1)
SELECT 11, 'x1' FROM DUAL
WHERE NOT EXISTS (SELECT id1 FROM mytble WHERE id1 = 11);


UPDATE mytable SET t1 = 'x1' WHERE id1 = 11;
小开

到目前为止，没有一个给出的答案是在并发访问面前是安全的，正如Tim Sylvester的评论所指出的那样，并将在种族的情况下引发异常。为了解决这个问题，必须将插入/更新组合包装在某种循环语句中，以便在出现异常时重新执行整个操作。

举个例子，下面是Grommit的代码如何被包装在一个循环中，以使其在并发运行时安全:

PROCEDURE MyProc (
...
) IS
BEGIN
LOOP
BEGIN
MERGE INTO Employee USING dual ON ( "id"=2097153 )
WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
WHEN NOT MATCHED THEN INSERT ("id","last","name")
VALUES ( 2097153,"smith", "john" );
EXIT; -- success? -> exit loop
EXCEPTION
WHEN NO_DATA_FOUND THEN -- the entry was concurrently deleted
NULL; -- exception? -> no op, i.e. continue looping
WHEN DUP_VAL_ON_INDEX THEN -- an entry was concurrently inserted
NULL; -- exception? -> no op, i.e. continue looping
END;
END LOOP;
END;
< p >注意:在事务模式SERIALIZABLE中，顺便说一句，我不建议这样做，你可能会遇到 ORA-08177:不能序列化此事务的访问异常代替
小开

多年来，我一直在使用第一个代码示例。注意notfound而不是count。

UPDATE tablename SET val1 = in_val1, val2 = in_val2
WHERE val3 = in_val3;
IF ( sql%notfound ) THEN
INSERT INTO tablename
VALUES (in_val1, in_val2, in_val3);
END IF;

下面的代码可能是新的和改进的代码

MERGE INTO tablename USING dual ON ( val3 = in_val3 )
WHEN MATCHED THEN UPDATE SET val1 = in_val1, val2 = in_val2
WHEN NOT MATCHED THEN INSERT
VALUES (in_val1, in_val2, in_val3)

在第一个示例中，更新执行索引查找。为了更新右边行，它必须这样做。Oracle打开一个隐式游标，我们使用它来包装相应的插入，这样我们就知道只有当键不存在时才会发生插入。但是插入是一个独立的命令，它必须进行第二次查找。我不知道merge命令的内部工作原理，但由于该命令是一个单独的单元，Oracle可以通过单个索引查找执行正确的插入或更新。

我认为合并更好，当你有一些处理要做，这意味着从一些表中获取数据，并更新一个表，可能插入或删除行。但是对于单行情况，您可以考虑第一个情况，因为语法更常见。


提交答案