Oracle Equivalent to MySQL INSERT IGNORE?
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?
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 USING ( SELECT 42 as the_pk_value, 'some_value' as some_column FROM dual ) t on (yt.pk = t.the_pke_value) WHEN NOT MATCHED THEN 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 LOG ERRORS INTO errlog ('some comment') REJECT LIMIT UNLIMITED;
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
See the manual for details