如果行不存在，则 Oracle 插入

2021-12-06 00:00:00 sql insert oracle 
insert ignore into table1 
select 'value1',value2 
from table2 
where table2.type = 'ok'

当我运行它时，我收到错误缺少 INTO 关键字".

When I run this I get the error "missing INTO keyword".

推荐答案

当我运行它时，我收到错误缺少 INTO 关键字".

When I run this I get the error "missing INTO keyword" .

因为 IGNORE 不是 Oracle 中的关键字.那是 MySQL 语法.

Because IGNORE is not a keyword in Oracle. That is MySQL syntax.

您可以做的是使用 MERGE.

What you can do is use 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)
/

从 Oracle 10g 开始，我们可以在不处理两个分支的情况下使用合并.在 9i 中，我们不得不使用虚拟" MATCHED 分支.

From Oracle 10g we can use merge without handling both branches. In 9i we had to use a "dummy" MATCHED branch.

在更古老的版本中，唯一的选择是:

In more ancient versions the only options were either :

  1. 在发出 INSERT 之前(或在子查询中)测试该行是否存在；
  2. 使用 PL/SQL 执行 INSERT 并处理任何由此产生的 DUP_VAL_ON_INDEX 错误.

相关文章