MySQL Duplicate entry for key on UPDATE

Question

I have a unique index on a column named label, but for some strange reason why I try and do an update like:

UPDATE books SET label = 'foo bar', title = 'something new', modified = UTC_TIMESTAMP();

And there already exists a row with label = 'foo bar' this errors:

 #1062 - Duplicate entry 'foo bar' for key 'label'

How can I make MySQL do the update? This shouldn't be breaking because technically there is still just one row with the key foo bar.

Thanks.

Answer 1

This SQL query is trying to update every single record in the books table with those values, because you have no WHERE clause. Its failing because you can only have one record with that label value yet the query wants to set them all to it.

I think possibly you are not executing the query you intended. Maybe you meant to update the title and time for the record with that label. Check your syntax.