Why MySQL UPDATE Seems to Do Nothing and How to Fix It

Developers often ask why an UPDATE statement in MySQL appears to run without changing any rows even though the syntax is correct.

Problematic SQL Statement

The original UPDATE used the syntax col1=value1 AND col2=value2 , which the developer believed should update multiple columns.

Before and After Records

Images show the record state before execution and after execution, demonstrating that the update actually took effect, contrary to the developer’s impression.

Why It Happened

MySQL’s UPDATE syntax expects a comma‑separated list of assignments, not the logical operator AND. Using AND turns the second part into a logical expression; if it evaluates to false (0), MySQL treats it as a value assignment, leading to unexpected results such as owner_code=0.

Correct Syntax

The proper way to update several columns is:

UPDATE table_name SET col1 = value1, col2 = value2 WHERE ...;

Verification

Re‑running the corrected statement produces the expected changes.

Takeaway

When updating multiple fields in a single UPDATE statement, separate assignments with commas, not the AND keyword. Using AND creates a logical expression that MySQL evaluates to 0 (false), which can unintentionally modify data.

Source: ju.outofmemory.cn/entry/336774