相当于INSERT IGNORE的Oracle [英] Oracle equivalent of INSERT IGNORE

问题描述

我在 Oracle等同于MySQL INSERT IGNORE上找到了一个非常相似的主题? 但是，我无法对任何建议的解决方案进行工作.我的情况有点特殊，因为我的表只包含1个字段，这是主键.在下文中，我们将字段称为"id"，并将表称为"myTable".

I found a very similar topic on Oracle Equivalent to MySQL INSERT IGNORE? However, I could not make work any of the proposed solutions. My case is a little special as my table does contains only 1 field, which is the primary key. Let's call the field "id" and the table "myTable" in the following.

使用MERGE

merge into myTable t1 from (
    select 42 as the_pk_value, 'TEST' as some_column from dual
) t2 on (t1.id = t2.the_pk_value)
when not matched then 
    insert (id) values (t2.the_pk_value);

第一次尝试会给出SQL Error: ORA-02012: missing USING keyword错误消息.我必须承认我不理解建议的语法，所以在适应我的仅pk表时，也许我搞砸了.但是，由于显示的示例未使用任何USING关键字，因此我不知道我的错误可能在哪里.

This first attempt gives a SQL Error: ORA-02012: missing USING keyword error message. I have to admit that I did NOT understand the proposed syntax, so maybe I messed something when adapting to my pk-only-table. But as the showed example did not use any USING keyword, I dont understand where my error can be.

使用提示

insert /*+ ignore_row_on_dupkey_index(SPSECU, PK_SPSECU) */ into myTable (id) values ('TEST');

这确实可以通过SQL Developer进行工作，但是不适用于Java(我怀疑OJDBC驱动程序会删除注释以减小传输大小.顺便说一句，我无法弄清楚我正在使用哪个JDBC驱动程序... Spring Tool源包似乎没有任何进一步配置即可连接到Oracle数据库.我只看到安装了DERBY默认驱动程序.

This does work through SQL Developer, however it does not work from Java (I suspect OJDBC Driver to remove the comments to reduce transfer size. BTW I could not figure out which JDBC driver I'm using... the Spring Tool Source bundle seems to connect without any further configuration to the Oracle Database. I see only a DERBY default driver installed.

使用不存在

我无法使该语法起作用.这是我写的:

I could not make this syntax work. Here is what I wrote :

insert into myTable (id) values ('TEST') where not exists (select id from myTable where id='TEST');

此版本出现SQL Error: ORA-00933: SQL command not properly ended错误.

使用插入选择

我对knagaev提出的解决方案一无所知...试图使其适应我的餐桌给了我这个:

I did not understand anything of the proposed solution by knagaev... trying to adapt it to my table gave me this :

insert into myTable t1 select id from myTable t2 where not exists (select 1 from t1 where t1.id = 'TEST');

有人可以帮助我吗?我已经习惯了MySQL INSERT IGNORE简单语法，并且在Oracle(使用11g版本)上还很新.

Can someone help me ? I'm used to MySQL INSERT IGNORE simple syntax and am quite new on Oracle (using 11g version).

推荐答案

合并使用using而不是from:

merge into myTable t1 USING (
    select 42 as the_pk_value, 'TEST' as some_column from dual
) t2 on (t1.id = t2.the_pk_value)

这篇关于相当于INSERT IGNORE的Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！