SQL 如果不存在则插入 oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1702832/
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
insert if not exists oracle
提问by cwallenpoole
I need to be able to run an Oracle query which goes to insert a number of rows, but it also checks to see if a primary key exists and if it does, then it skips that insert. Something like:
我需要能够运行一个 Oracle 查询来插入多行，但它还会检查主键是否存在，如果存在，则跳过该插入。就像是：
INSERT ALL
IF NOT EXISTS( SELECT 1 WHERE fo.primary_key='bar' )
(
INSERT INTO
schema.myFoo fo ( primary_key, value1, value2 )
VALUES
('bar','baz','bat')
),
IF NOT EXISTS( SELECT 1 WHERE fo.primary_key='bar1' )
(
INSERT INTO
schema.myFoo fo ( primary_key, value1, value2 )
VALUES
('bar1','baz1','bat1')
)
SELECT * FROM schema.myFoo;
Is this at all possible with Oracle?
这对 Oracle 来说完全可能吗？
Bonus points if you can tell me how to do this in PostgreSQL or MySQL.
如果您能告诉我如何在 PostgreSQL 或 MySQL 中执行此操作，请加分。
采纳答案by erikkallen
The statement is called MERGE. Look it up, I'm too lazy.
该语句称为 MERGE。看看吧，我太懒了。
Beware, though, that MERGE is not atomic, which could cause the following effect (thanks, Marius):
但是请注意，MERGE 不是原子的，这可能会导致以下效果（感谢 Marius）：
SESS1:
SESS1：
create table t1 (pk int primary key, i int);
create table t11 (pk int primary key, i int);
insert into t1 values(1, 1);
insert into t11 values(2, 21);
insert into t11 values(3, 31);
commit;
SESS2: insert into t1 values(2, 2);
SESS2： insert into t1 values(2, 2);
SESS1:
SESS1：
MERGE INTO t1 d
USING t11 s ON (d.pk = s.pk)
WHEN NOT MATCHED THEN INSERT (d.pk, d.i) VALUES (s.pk, s.i);
SESS2: commit;
SESS2： commit;
SESS1: ORA-00001
SESS1： ORA-00001
回答by Michael Deardeuff
Coming late to the party, but...
聚会迟到了，但是……
With oracle 11.2.0.1 there is a semantic hintthat can do this: IGNORE_ROW_ON_DUPKEY_INDEX
在 oracle 11.2.0.1 中有一个语义提示可以做到这一点：IGNORE_ROW_ON_DUPKEY_INDEX
Example:
例子：
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(customer_orders,pk_customer_orders) */
into customer_orders
(order_id, customer, product)
values ( 1234, 9876, 'K598')
;
UPDATE: Although this hint works (if you spell it correctly), there are better approacheswhich don't require Oracle 11R2:
更新：虽然这个提示有效（如果你拼写正确），但有更好的方法不需要 Oracle 11R2：
First approach—direct translation of above semantic hint:
第一种方法——直接翻译上述语义提示：
begin
insert into customer_orders
(order_id, customer, product)
values ( 1234, 9876, 'K698')
;
commit;
exception
when DUP_VAL_ON_INDEX
then ROLLBACK;
end;
Second aproach—a lotfaster than both above hints when there's a lot of contention:
其次的形式给出，一个很多比上述两者时有很多争论的提示更快：
begin
select count (*)
into l_is_matching_row
from customer_orders
where order_id = 1234
;
if (l_is_matching_row = 0)
then
insert into customer_orders
(order_id, customer, product)
values ( 1234, 9876, 'K698')
;
commit;
end if;
exception
when DUP_VAL_ON_INDEX
then ROLLBACK;
end;
回答by AlanG
This only inserts if the item to be inserted is not already present.
仅当要插入的项目不存在时才插入。
Works the same as:
工作原理与：
if not exists (...) insert ...
in T-SQL
在 T-SQL 中
insert into destination (DESTINATIONABBREV)
select 'xyz' from dual
left outer join destination d on d.destinationabbrev = 'xyz'
where d.destinationid is null;
may not be pretty, but it's handy :)
可能不漂亮，但它很方便:)
回答by OnionHead
We can combine the DUAL and NOT EXISTS to archive your requirement:
我们可以结合 DUAL 和 NOT EXISTS 来归档您的需求：
INSERT INTO schema.myFoo (
primary_key, value1, value2
)
SELECT
'bar', 'baz', 'bat'
FROM DUAL
WHERE NOT EXISTS (
SELECT 1
FROM schema.myFoo
WHERE primary_key = 'bar'
);
回答by W_O_L_F
If you do NOT want to merge in from an other table, but rather insert new data... I came up with this. Is there perhaps a better way to do this?
如果您不想从其他表合并，而是插入新数据......我想出了这个。也许有更好的方法来做到这一点？
MERGE INTO TABLE1 a
USING DUAL
ON (a.C1_pk= 6)
WHEN NOT MATCHED THEN
INSERT(C1_pk, C2,C3,C4)
VALUES (6, 1,0,1);
回答by user151019
It that code is on the client then you have many trips to the server so to eliminate that.
如果该代码在客户端上，那么您需要多次访问服务器以消除这种情况。
Insert all the data into a temportary table say T with the same structure as myFoo
将所有数据插入到与 myFoo 结构相同的临时表 T 中
Then
然后
insert myFoo
select *
from t
where t.primary_key not in ( select primary_key from myFoo)
This should work on other databases as well - I have done this on Sybase
这也应该适用于其他数据库 - 我已经在 Sybase 上完成了这项工作
It is not the best if very few of the new data is to be inserted as you have copied all the data over the wire.
如果要插入的新数据很少，因为您已经通过网络复制了所有数据，这不是最好的方法。
回答by Selin
DECLARE
tmp NUMBER(3,1);
BEGIN
SELECT COUNT(content_id) INTO tmp FROM contents WHERE (condition);
if tmp != 0 then
INSERT INTO contents VALUES (...);
else
INSERT INTO contents VALUES (...);
end if;
END;
I used the code above. It is long, but, simple and worked for me. Similar, to Micheal's code.
我使用了上面的代码。它很长，但很简单，对我有用。类似于迈克尔的代码。
回答by zetzer
If your table is "independent" from others (I mean, it will not trigger a cascade delete or will not set any foreign keys relations to null), a nice trick could be to first DELETE the row and then INSERT it again. It could go like this:
如果您的表与其他表“独立”（我的意思是，它不会触发级联删除或不会将任何外键关系设置为空），一个不错的技巧可能是首先删除该行然后再次插入它。它可以是这样的：
DELETE FROM MyTable WHERE prop1 = 'aaa'; //assuming it will select at most one row!
从 MyTable 中删除 prop1 = 'aaa'; //假设它最多选择一行！
INSERT INTO MyTable (prop1, ...) VALUES ('aaa', ...);
INSERT INTO MyTable (prop1, ...) VALUES ('aaa', ...);
If your are deleting something which does not exist, nothing will happen.
如果您要删除不存在的内容，则不会发生任何事情。
回答by RaZieRSarE
INSERT INTO schema.myFoo ( primary_key , value1 , value2 )
SELECT 'bar1' AS primary_key ,'baz1' AS value1 ,'bat1' AS value2 FROM DUAL WHERE (SELECT 1 AS value FROM schema.myFoo WHERE LOWER(primary_key) ='bar1' AND ROWNUM=1) is null;
回答by Marius Burz
This is an answer to the comment posted by erikkallen:
这是对erikkallen发表的评论的回答：
You don't need a temp table. If you only have a few rows, (SELECT 1 FROM dual UNION SELECT 2 FROM dual) will do. Why would your example give ORA-0001? Wouldn't merge take the update lock on the index key and not continue until Sess1 has either committed or rolled back? – erikkallen
您不需要临时表。如果您只有几行， (SELECT 1 FROM dual UNION SELECT 2 FROM dual) 就可以了。为什么你的例子会给出 ORA-0001？合并不会在索引键上获取更新锁，并且在 Sess1 提交或回滚之前不会继续吗？– 埃里卡伦
Well, try it yourself and tell me whether you get the same error or not:
好吧，自己尝试一下并告诉我您是否遇到相同的错误：
SESS1:
SESS1：
create table t1 (pk int primary key, i int);
create table t11 (pk int primary key, i int);
insert into t1 values(1, 1);
insert into t11 values(2, 21);
insert into t11 values(3, 31);
commit;
SESS2: insert into t1 values(2, 2);
SESS2： insert into t1 values(2, 2);
SESS1:
SESS1：
MERGE INTO t1 d
USING t11 s ON (d.pk = s.pk)
WHEN NOT MATCHED THEN INSERT (d.pk, d.i) VALUES (s.pk, s.i);
SESS2: commit;
SESS2： commit;
SESS1: ORA-00001
SESS1： ORA-00001
