Why MySQL UPDATE with AND Fails and How to Correctly Update Multiple Columns

Recently several developers asked why an UPDATE statement that appears syntactically correct does not modify the intended rows in MySQL. The problem is summarized as: In MySQL, using AND between column assignments in an UPDATE does not work.

Conclusion : When updating multiple fields in a single UPDATE, separate the assignments with commas, not with AND.

Phenomenon : A faulty UPDATE statement was executed on a test database. Before execution the record showed owner_code='13245' and owner_name='张三' . After execution the owner_name remained unchanged while owner_code became 0 , indicating a partial effect.

The original SQL used:

update apps set owner_code='43212' and owner_name='李四' where owner_code='13245' and owner_name='张三';

According to MySQL documentation, the SET clause expects a comma‑separated list of col_name = value assignments. The correct statement should be:

update apps set owner_code='43212', owner_name='李四' where owner_code='13245' and owner_name='张三';

Running the corrected statement yields the expected result, updating both columns.

Why the original fails : Using AND makes the assignment part a logical expression. MySQL evaluates ('43212' AND owner_name='李四') as a boolean; since owner_name='李四' is false, the whole expression evaluates to false, which MySQL treats as 0 . Hence owner_code is set to 0 while owner_name is never assigned.

In summary, multiple column updates must use commas, and using AND turns the assignment into a logical expression that can produce unintended zero values.