Oracle equivalent of MySQL update ignore

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 do开发者_StackOverflow社区ne using the ignore command. Is there an equivalent in Oracle?

---

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

---

It's hard to definitively say no (Oracle is big) but in 15 years of database programming I've never seen a feature like this in Oracle. You can disable constraints, but that's not the same thing as you're trying to accomplish here.

One workaround would be to write some PL/SQL that processes the table you're updating row-by-row, attempts to make the update, and swallows any errors. That's not going to be efficient, but it will work. But in every scenario I can imagine (unless you have a very, very complex table) you should be able to write your update query to include appropriate subqueries that work around the constraints. The exact strategy would depend on the tables and on the query.