I am trying to run an update on a table which I know will cause some duplicate key errors. I am therefore using UPDATE IGNORE as I understood this would ignore these errors and still update the rest of the table, but this is not the case – it just fails with an error:
[SQL] UPDATE IGNORE `activity-customer` set `customerid` = 12345 where `customerid` = 56789
[Err] 1062 - Duplicate entry '12345-180221' for key 'PRIMARY'
SQL_MODE is not set to STRICT (it is empty)
This is using MySQL 5.5.47.
I'm very confused! Any help appreciated!
Best Answer
I managed to track this one down. There was a trigger on the table which was updating / inserting into another table.
The update/insert on this trigger did NOT have an ignore and it was actually that which was throwing the key error, not the original update.
Adding the ignore into that trigger code resolved the problem.