73123

Oracle equivalent of INSERT IGNORE

Question:

I found a very similar topic on <a href="https://stackoverflow.com/questions/9332360/oracle-equivalent-to-mysql-insert-ignore" rel="nofollow">Oracle Equivalent to MySQL INSERT IGNORE?</a> 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.

<strong>Using MERGE</strong>

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);

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.

<strong>Using hint</strong>

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

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.

<strong>Using NOT EXISTS</strong>

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');

I get an SQL Error: ORA-00933: SQL command not properly ended error with this version.

<strong>Using insert select</strong>

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');

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

Answer1:

Merge uses using instead of 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)

Recommend

  • postgresql duplicate key violates unique constraint
  • Query the Next Time Interval
  • Drive EndDate of Current Row From StarDate of Next Row
  • Delete all records having value 5x lower than the second highest value
  • The script does not work in IE. How can I fix it?
  • Excel changes conditional formatting formula
  • iBatis using a set for a resultMap as well as a parameterMap
  • Writing to a textBox using two threads
  • How to perform a left join in SQLALchemy?
  • TypeScript Mapped Types: Get element type of array
  • Populate checkbox from database
  • overlapping appointments using the entity framework
  • Sort by a column in a union query in SqlAlchemy SQLite
  • Is an if-let or a normal if condition better?
  • ASP.NET MVC2 Error: No parameterless constructor defined for this object
  • matching similar elements in between two lists
  • Efficient User-Agent Regex to find Safari in Python
  • How to 'create temp table as select' in Slick?
  • pyodbc doesn't report sql server error
  • Typescript - Unable to get 'import' statement to function
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Display issues when we change from one jquery mobile page to another in firefox
  • Deselecting radio buttons while keeping the View Model in synch
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?
  • Running a C# exe file
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • JSON with duplicate key names losing information when parsed
  • Convert array of 8 bytes to signed long in C++
  • Jquery - Jquery Wysiwyg return html as a string
  • Transpose CSV data with awk (pivot transformation)
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Hits per day in Google Big Query
  • Understanding cpu registers
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices