MySQL UPDATE Behavior
mysql> update tablename set fieldname = 'C200900674' where fieldname is 'C200900673';
ERROR 1062 (23000): Duplicate entry 'C200900674-2008-0-1' for key 1
Any thoughts or suggestions on this? Someone accidentally made this update with a minus sign instead of an equal sign. He seems to be trying to change all records less than this value? Despite being alphanumeric and really quite incomplete. Also, the record count was updated before it got this error and there was no feedback at all. Nothing like "Query OK, X rows affected (0.00 sec)", so we had no idea how many of them were changed. autocommit = 1, so there is no rollback option.
Anyway, just look for clues or pointers to this. Why did this request do anything at all, it looks like it should have returned an error to me. Apart from the obvious answer, keeping out inexperienced admins, of course, there are shameless things.
a source to share
Whenever in doubt as to how Mysql interprets the WHERE clause, reverse it to SELECT.
SELECT fieldname - 'C200900673' FROM tablename;
and
SELECT fieldname FROM tablename WHERE fieldname - 'C200900673';
See what value the first selection returns and what rows the second.
Unfortunately, since Mysql is pretty weak on numeric / string conversions, especially on the 4.x series, and even on the lax 5.x heck ... even strict, it's hard to tell exactly what went wrong without all the details of your Mysql configuration. It could be because the field name was appended to some number, as well as "C200900673" basically working:
update tablename set fieldname = 'C200900674' where NUMBER - NUMBER;
What can be translated into:
update tablename set fieldname = 'C200900674' where 1;
Anyway, I hope you have a backup!
a source to share
If your table uses the InnoDB storage engine, there was no harm. Even with autocommit = 1, only an all-or-nothing request is executed. The fact that you received an ERROR message is proof that the database did not touch your data. Whenever you receive an ERROR, the "x rows affected" message is omitted.
Even if the uniqueness constraint was not violated, the request would have failed with another error:
ERROR 1292 (22007): Invalid truncated DOUBLE value: 'fieldname'
This is because the minus sign made MySQL try to compute the value of the field content minus something else. This does not work. You just didn't see this error because the other one "got there first."
a source to share