0

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!

Improve this question
4
  • 2
    Can you please provide the exact table structure and the result of SELECT * FROM activity-customer WHERE customerid = 56789?
    – Riedsio
    May 25, 2017 at 18:50
  • Do you get errors or warnings?
    – ypercubeᵀᴹ
    May 25, 2017 at 19:07
  • I get an error - as above- [Err] 1062 - Duplicate entry I will post table structure tomorrow
    – CRM Dev
    May 25, 2017 at 22:09
  • Yes, it would be good to check - what structure, may be some other settings, because I can not reproduce error not on one from several versions and even with sql_mode strict, always work as expected when used IGNORE keyword
    – a_vlad
    May 26, 2017 at 1:17

1 Answer 1

Reset to default
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.