Mind your MySQL update syntax

In: Fun

22 Dec 2006

Just noticed an insidious “bug” in MySQL when you use “AND”s instead of commas to separate the fields you are updating in your SET clause. Being a little rusty in SQL, I entered something like the query below while trying to make a quick fix to some records:

UPDATE characters
SET alignment = 'Neutral Evil'
AND last_name = 'Majere'
WHERE first_name = 'Raistlin'

Much to my consternation, the record appeared to have disappeared when I tried to query for it. After much bewildered cursing, I came across MySQL bug #15928: update syntax error not detected (this is not an actual bug though). As it turns out, MySQL does a logical AND and the result is that the ‘alignment’ field is set to ‘0’ since 'Neutral Evil' AND last_name = 'Majere' results in false. And MySQL casts it to ‘0’. Grrr. Luckily I was able to revert the changes by finding the record again with the new ‘0’ alignment value (was using an auto-committing MySQL GUI client, no ROLLBACK available).

Comments are closed.