SQL Oracle：如何 UPSERT（更新或插入表？）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/237327/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: how to UPSERT (update or insert into a table?)
提问by Mark Harrison
The UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data:
UPSERT 操作更新或在表中插入一行，具体取决于该表是否已有与数据匹配的行：
if table t has a row exists that has key X:
update t set mystuff... where mykey=X
else
insert into t mystuff...
Since Oracle doesn't have a specific UPSERT statement, what's the best way to do this?
由于 Oracle 没有特定的 UPSERT 语句，执行此操作的最佳方法是什么？
采纳答案by Tony Andrews
An alternative to MERGE (the "old fashioned way"):
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;
回答by Mark Harrison
The MERGE statementmerges data between two tables. Using DUAL allows us to use this command. Note that this is not protected against concurrent access.
在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
回答by MyDeveloperDay
The dual example above which is in PL/SQL was great becuase I wanted to do something similar, but I wanted it client side...so here is the SQL I used to send a similar statement direct from some C#
上面在 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" )
However from a C# perspective this provide to be slower than doing the update and seeing if the rows affected was 0 and doing the insert if it was.
但是，从 C# 的角度来看，这比执行更新和查看受影响的行是否为 0 并执行插入（如果是）要慢。
回答by Brian Schmitt
Another alternative without the exception check:
没有异常检查的另一种选择：
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;
回答by test1
- insert if not exists
- update:
- 如果不存在则插入
- 更新：
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;
回答by Eugene Beresovsky
None of the answers given so far is safe in the face of concurrent accesses, as pointed out in Tim Sylvester's comment, and will raise exceptions in case of races. To fix that, the insert/update combo must be wrapped in some kind of loop statement, so that in case of an exception the whole thing is retried.
正如蒂姆·西尔维斯特 (Tim Sylvester) 的评论中所指出的那样，目前为止给出的答案在并发访问时都不是安全的，并且会在发生竞争时引发异常。为了解决这个问题，插入/更新组合必须包含在某种循环语句中，以便在发生异常时重试整个过程。
As an example, here's how Grommit's code can be wrapped in a loop to make it safe when run concurrently:
例如，以下是如何将 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;
N.B. In transaction mode SERIALIZABLE, which I don't recommend btw, you might run into
ORA-08177: can't serialize access for this transactionexceptions instead.
NB 在事务模式中SERIALIZABLE，顺便说一句，我不推荐这种模式，您可能会遇到
ORA-08177: can't serialize access for this transactionexceptions 相反。
回答by Hubbitus
I'd like Grommit answer, except it require dupe values. I found solution where it may appear once: http://forums.devshed.com/showpost.php?p=1182653&postcount=2
我想要 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);
回答by AnthonyVO
A note regarding the two solutions that suggest:
关于建议的两种解决方案的说明：
1) Insert, if exception then update,
1）插入，如果异常则更新，
or
或者
2) Update, if sql%rowcount = 0 then insert
2) 更新，如果 sql%rowcount = 0 则插入
The question of whether to insert or update first is also application dependent. Are you expecting more inserts or more updates? The one that is most likely to succeed should go first.
是先插入还是先更新的问题也取决于应用程序。你期待更多的插入还是更多的更新？最有可能成功的应该先行。
If you pick the wrong one you will get a bunch of unnecessary index reads. Not a huge deal but still something to consider.
如果你选错了，你会得到一堆不必要的索引读取。没什么大不了的，但仍然需要考虑。
回答by Arturo Hernandez
I've been using the first code sample for years. Notice notfound rather than count.
我多年来一直在使用第一个代码示例。注意 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;
The code below is the possibly new and improved code
下面的代码可能是新的和改进的代码
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)
In the first example the update does an index lookup. It has to, in order to update the right row. Oracle opens an implicit cursor, and we use it to wrap a corresponding insert so we know that the insert will only happen when the key does not exist. But the insert is an independent command and it has to do a second lookup. I don't know the inner workings of the merge command but since the command is a single unit, Oracle could have execute the correct insert or update with a single index lookup.
在第一个示例中，更新执行索引查找。为了更新正确的行，它必须这样做。Oracle 打开一个隐式游标，我们用它来包装相应的插入，因此我们知道插入只会在键不存在时发生。但是插入是一个独立的命令，它必须进行第二次查找。我不知道合并命令的内部工作原理，但由于该命令是一个单元，Oracle 可以通过单个索引查找执行正确的插入或更新。
I think merge is better when you do have some processing to be done that means taking data from some tables and updating a table, possibly inserting or deleting rows. But for the single row case, you may consider the first case since the syntax is more common.
我认为当您确实需要完成一些处理时，合并会更好，这意味着从某些表中获取数据并更新表，可能插入或删除行。但是对于单行情况，您可以考虑第一种情况，因为语法更常见。
回答by Bechyňák Petr
Copy & paste example for upserting one table into another, with MERGE:
使用 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
Result:
结果：
- b 4 5
- c 3 3
- a 1 1
- 4 5
- 3 3
- 1 1
