Oracle equivalent of MySQL update ignore

Question

I need to run an Oracle query that would violate unique constraits, only on the records that would not violate constraints. In MySQL I believe this can be done using the ignore command. Is there an equivalent in Oracle?

Answer 1

You can use Oracle's error logging feature for this:

First you need to create a table that will later contain the ignored rows:

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('YOUR_TABLE', 'YOUR_TABLE_ERROR_LOG');

That creates a table called YOUR_TABLE_ERROR_LOG for the table named YOUR_TABLE (obviously you only need to do that once).

When you run your UPDATE you need to add the LOG ERRORS clause:

UPDATE your_table
   SET ...
WHERE ...
LOG ERRORS INTO YOUR_TABLE_ERROR_LOG ('UPDATE running at '||to_char(sysdate, 'yyyy-MM-dd HH24:MI:SS')) 
REJECT LIMIT UNLIMITED;

The string that is specified is an arbitrary value that helps you identify the action that generated the errors.

After the update you can query the table YOUR_TABLE_ERROR_LOG to see which errors occurred and why. If you are not interested in the errors, simply truncate the error log table afterwards.

For more details see the manual:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10008.htm#BCEFBFCD

Edit 2014-10-27

Since Oracle 11.2 there is a new hint named CHANGE_DUPKEY_ERROR_INDEX which can be used for this purpose. I have never tried this though.

Details in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#CHDIFFJE

For INSERT operations there is a similar hint named IGNORE_ROW_ON_DUPKEY_INDEX:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#CHDEGDDG

Some examples:

• http://www.morganslibrary.org/reference/hints.html#seh
• http://thinktibits.blogspot.de/2014/07/Oracle-Change-dupkey-error-index-hint-example.html