sql插入oracle表,sql - Oracle：如何UPSERT（更新或插入表？）

sql - Oracle：如何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语句，最好的方法是什么？

12个解决方案

192 votes

MERGE语句合并两个表之间的数据。 使用DUAL允许我们使用此命令。 请注意，这不受并发访问保护。

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

Mark Harrison answered 2019-02-22T18:14:02Z

93 votes

PL / SQL中的上述双重示例非常棒，因为我想做类似的事情，但我想要客户端...所以这里是我用来直接从一些C＃发送类似语句的SQL

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，如果是，则执行插入操作。

MyDeveloperDay answered 2019-02-22T18:14:33Z

43 votes

没有例外检查的另一种选择：

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;

Brian Schmitt answered 2019-02-22T18:14:57Z

41 votes

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;

Tony Andrews answered 2019-02-22T18:13:38Z

22 votes

如果不存在则插入

更新：

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;

test1 answered 2019-02-22T18:15:22Z

22 votes

如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;

注： 在事务模式SERIALIZABLE，我不建议顺便说一句，你可能会碰到ORA-08177：无法序列化此事务异常的访问权限。

Eugene Beresovsky answered 2019-02-22T18:16:01Z

17 votes

我想要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);

Hubbitus answered 2019-02-22T18:16:25Z

8 votes

关于两种解决方案的说明：

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

要么

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

是否首先插入或更新的问题也取决于应用程序。 您是否期望更多插入或更多更新？ 最有可能成功的那个应该先行。

如果你选错了，你将获得一堆不必要的索引读取。 这不是一件大事，但仍有待考虑。

AnthonyVO answered 2019-02-22T18:17:18Z

7 votes

我多年来一直在使用第一个代码示例。 注意不要发现而不是计数。

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可以使用单个索引查找执行正确的插入或更新。

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

Arturo Hernandez answered 2019-02-22T18:18:10Z

0 votes

复制＆amp; 使用MERGE将一个表复制到另一个表的粘贴示例：

CREATE GLOBAL TEMPORARY TABLE t1

(id VARCHAR2(5) ,

value VARCHAR2(5),

value2 VARCHAR2(5)

)

ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE t2

(id VARCHAR2(5) ,

value VARCHAR2(5),

value2 VARCHAR2(5))

ON COMMIT DELETE ROWS;

ALTER TABLE t2 ADD CONSTRAINT PK_LKP_MIGRATION_INFO PRIMARY KEY (id);

insert into t1 values ('a','1','1');

insert into t1 values ('b','4','5');

insert into t2 values ('b','2','2');

insert into t2 values ('c','3','3');

merge into t2

using t1

on (t1.id = t2.id)

when matched then

update set t2.value = t1.value,

t2.value2 = t1.value2

when not matched then

insert (t2.id, t2.value, t2.value2)

values(t1.id, t1.value, t1.value2);

select * from t2

结果：

b 4 5

c 3 3

1 1

Bechyňák Petr answered 2019-02-22T18:19:06Z

-3 votes

试试这个，

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

)

;

r4bitt answered 2019-02-22T18:19:27Z

-6 votes

来自[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;

Anon answered 2019-02-22T18:19:59Z