oracle equivalent to mysql insert ignore

oracle equivalent to mysql insert ignore  using -'oracle,insert,duplicates'

I need to update a query so that it checks that a duplicate entry does not exist before insertion.  In MySQL I can just use INSERT IGNORE so that if a duplicate record is found it just skips the insert, but I can't seem to find an equivalent option for Oracle.  Any suggestions?

asked Oct 13, 2015 by badhwar.rohit
0 votes

4 Answers

0 votes

Check out the MERGE statement. This should do what you want - it's the WHEN NOT MATCHED clause that will do this.

Do to Oracle's lack of support for a true VALUES() clause the syntax for a single record with fixed values is pretty clumsy though:

MERGE INTO your_table yt
   SELECT 42 as the_pk_value, 
          'some_value' as some_column
   FROM dual
) t on ( = t.the_pke_value) 
   INSERT (pk, the_column)
   VALUES (t.the_pk_value, t.some_column);

A different approach (if you are e.g. doing bulk loading from a different table) is to use the "Error logging" facility of Oracle. The statement would look like this:

 INSERT INTO your_table (col1, col2, col3)
 SELECT c1, c2, c3
 FROM staging_table

Afterwards all rows that would have thrown an error are available in the table errlog. You need to create that errlog table (or whatever name you choose) manually before running the insert using DBMS_ERRLOG.CREATE_ERROR_LOG.

See the manual for details

answered Oct 13, 2015 by rolvyrf
0 votes

I don't think there is but to save time you can attempt the insert and ignore the inevitable error:


   insert into table_a( col1, col2, col3 )
   values ( 1, 2, 3 );

   exception when dup_val_on_index then 


This will only ignore exceptions raised specifically by duplicate primary key or unique key constraints; everything else will be raised as normal.

If you don't want to do this then you have to select from the table first, which isn't really that efficient.

answered Oct 13, 2015 by deepak gupta
0 votes

If you're on 11g you can use the hint IGNORE_ROW_ON_DUPKEY_INDEX:

SQL> create table my_table(a number, constraint my_table_pk primary key (a));

Table created.

SQL> insert /*+ ignore_row_on_dupkey_index(my_table, my_table_pk) */
  2  into my_table
  3  select 1 from dual
  4  union all
  5  select 1 from dual;

1 row created.
answered Oct 13, 2015 by yashwantpinge
0 votes

Another variant

Insert into my_table (student_id, group_id)
select distinct p.studentid, g.groupid 
from person p, group g
where NOT EXISTS (select 1
                 from my_table a
                 where a.student_id = p.studentid
                 and a.group_id = g.groupid)

or you could do

Insert into my_table (student_id, group_id)
select distinct p.studentid, g.groupid 
from person p, group g
select student_id, group_id
from my_table 
answered Oct 13, 2015 by deepak