Error Code: 1062. Duplicate entry '####' for key 'key_id' on update statement

Question

I am trying to update a record in a table and it's throwing the following error:

Error Code: 1062. Duplicate entry '5452' for key 'device_id'

from

update device_info set  serial_number = '728015162815' , ip_address = '192.168.77.121'   , location_code = '1', battery_status = 'Not Charging : 36%'       
where device_id = 5452

What is going on here? I can understand this error on an insert, but this is getting thrown on an update statement.

Answer 1

For the record, comments above show the solution.

Mysterious errors that seem impossible given the SQL statement you run might be caused by another SQL statement. How can this happen? It could be run by a trigger that is spawned by your UPDATE.

The trigger might be inserting a row to some other table, resulting in a duplicate key error. Or the trigger could change a value in a unique column of the current row being updated, that could also result in the same error.

This will show you what triggers exist:

SHOW TRIGGERS;