SQL 如果行不存在，则 oracle 插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3147874/
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 insert if row not exists
提问by coder247
insert ignore into table1
select 'value1',value2
from table2
where table2.type = 'ok'
When I run this I get the error "missing INTO keyword" .
当我运行它时，我收到错误“缺少 INTO 关键字”。
Any ideas ?
有任何想法吗 ？
回答by APC
When I run this I get the error "missing INTO keyword" .
当我运行它时，我收到错误“缺少 INTO 关键字”。
Because IGNORE is not a keyword in Oracle. That is MySQL syntax.
因为 IGNORE 在 Oracle 中不是关键字。那是 MySQL 语法。
What you can do is use MERGE.
您可以做的是使用 MERGE。
merge into table1 t1
using (select 'value1' as value1 ,value2
from table2
where table2.type = 'ok' ) t2
on ( t1.value1 = t2.value1)
when not matched then
insert values (t2.value1, t2.value2)
/
From Oracle 10g we can use merge without handling both branches. In 9i we had to use a "dummy" MATCHED branch.
从 Oracle 10g 开始，我们可以在不处理两个分支的情况下使用合并。在 9i 中，我们不得不使用“虚拟” MATCHED 分支。
In more ancient versions the only options were either :
在更古老的版本中，唯一的选择是：
- test for the row's existence before issuing an INSERT (or in a sub-query);
- to use PL/SQL to execute the INSERT and handle any resultant DUP_VAL_ON_INDEX error.
- 在发出 INSERT 之前（或在子查询中）测试该行是否存在；
- 使用 PL/SQL 执行 INSERT 并处理任何由此产生的 DUP_VAL_ON_INDEX 错误。
回答by Rob van Wijk
Note that if you are lucky enough to work with version 11g Release 2, you can use the hint IGNORE_ROW_ON_DUPKEY_INDEX.
请注意，如果您有幸使用 11g 第 2 版，则可以使用提示IGNORE_ROW_ON_DUPKEY_INDEX。
INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(table1(id)) */ INTO table1 SELECT ...
From the documentation: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements006.htm#CHDEGDDG
从文档：http: //download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements006.htm#CHDEGDDG
An example from my blog: http://rwijk.blogspot.com/2009/10/three-new-hints.html
我博客的一个例子：http: //rwijk.blogspot.com/2009/10/three-new-hints.html
Regards, Rob.
问候，罗布。
回答by Tony Andrews
Because you typed the spurious word "ignore" between "insert" and "into"!!
因为您在“插入”和“插入”之间输入了虚假的“忽略”一词！！
insert ignore into table1 select 'value1',value2 from table2 where table2.type = 'ok'
Should be:
应该：
insert into table1 select 'value1',value2 from table2 where table2.type = 'ok'
From your question title "oracle insert if row not exists" I assume you thought "ignore" was an Oracle keyword that means "don't try to insert a row if it already exists". Maybe this works in some other DBMS, but it doesn't in Oracle. You could use a MERGE statement, or check for existence like this:
从您的问题标题“如果行不存在，则 oracle 插入”我假设您认为“忽略”是一个 Oracle 关键字，意思是“如果行已存在，则不要尝试插入行”。也许这适用于其他一些 DBMS，但不适用于 Oracle。您可以使用 MERGE 语句，或像这样检查是否存在：
insert into table1
select 'value1',value2 from table2
where table2.type = 'ok'
and not exists (select null from table1
where col1 = 'value1'
and col2 = table2.value2
);
