Why Misplaced Quotes in MySQL UPDATE Can Turn All Values to Zero

Introduction

Developers often encounter accidental data deletions or updates. In this case a production database needed to be fixed by executing about 120 UPDATE statements to modify the source_name field.

Process

The first UPDATE executed correctly, adding a prefix to the address string. After running the remaining statements, every source_name turned into 0. Inspection of the binlog revealed many statements like update tablename set source_name=0. The team used binlog2sql to parse the logs and generate flashback SQL for recovery.

Weird UPDATE Syntax

Some of the executed statements had misplaced quotation marks, e.g.: update tbl_name set str_col="xxx" = "yyy" Other variations observed were:

update tbl_name set (str_col="xxx") = "yyy"

update tbl_name set str_col=("xxx" = "yyy")

These forms are syntactically valid in MySQL but behave unexpectedly.

How MySQL Interprets the Condition

MySQL rewrites the expression as a nested comparison: ((`tbl_name`.`str_col` = 'xxx') = 'yyy') The inner comparison yields 1 if str_col equals 'xxx', otherwise 0. The outer comparison then compares that numeric result with the string 'yyy'. Because MySQL performs implicit conversion, the string 'yyy' becomes 0 (float conversion). Thus the outer comparison becomes 0 = 0 or 1 = 0, both evaluating to 0 or 1 accordingly. In the shown case the result is always 0 = 0, which is true, so the UPDATE effectively sets str_col to 0 for every row.

Impact on SELECT Statements

A similar condition in a SELECT query:

select id, str_col from tbl_name where str_col = "xxx" = "yyy";

is parsed as the same nested comparison, which evaluates to true for all rows, causing the query to return the entire table.

Conclusion

When writing SQL, pay close attention to the placement of quotation marks. Even if a statement appears syntactically correct, misplaced quotes can lead to logical errors that affect all rows. Always test statements in a non‑production environment and use IDE syntax highlighting to catch such issues early.