Why MySQL UPDATE Fails with AND and How to Use Comma‑Separated Assignments

Several developers asked why an UPDATE statement that looks syntactically correct does not appear to modify rows in MySQL. The author reproduced the issue in a test database, showing the original record, the executed SQL (shown as an image), and the resulting record after execution (also images). Although the update did affect the data, the developer’s perception was that nothing changed.

Reviewing the MySQL documentation revealed that the assignment_list in an UPDATE must be a comma‑separated list of col_name = value pairs. The problematic statement used the logical operator AND between assignments, which is not valid for updating multiple columns.

The correct syntax, illustrated with an image, uses commas to separate assignments. Re‑running the statement with commas produced the expected result, as confirmed by the before‑and‑after screenshots.

Further investigation explained why using AND yields strange results such as owner_code = 0. In MySQL, an expression like ('43212' AND owner_name='李四') is evaluated as a logical expression; since owner_name='李四' is false, the whole expression evaluates to false, which MySQL treats as 0. An equivalent expression image demonstrates this behavior.

Key takeaway: When updating multiple columns in a single UPDATE statement, separate the assignments with commas, not with the logical AND operator.