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.:

<code>update tbl_name set str_col="xxx" = "yyy"</code>

Other variations observed were:

<code>update tbl_name set (str_col="xxx") = "yyy"</code>

<code>update tbl_name set str_col=("xxx" = "yyy")</code>

These forms are syntactically valid in MySQL but behave unexpectedly.

How MySQL Interprets the Condition

MySQL rewrites the expression as a nested comparison:

<code>((`tbl_name`.`str_col` = 'xxx') = 'yyy')</code>

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:

<code>select id, str_col from tbl_name where str_col = "xxx" = "yyy";</code>

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.