Why MySQL UPDATE Fails with AND and How to Fix It

Problem

Developers often report that an UPDATE statement executes without error but appears to have no effect on the data.

Incorrect SQL

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

The statement uses AND between column assignments, which is syntactically accepted but semantically wrong.

Observed Result

Before execution the row contains:

After execution the row shows:

The owner_name value remains unchanged, while owner_code becomes 0, indicating an unexpected side‑effect.

Analysis

MySQL’s UPDATE syntax expects a comma‑separated list of column=value pairs. Using AND turns the expression into a logical operation: ('43212' AND owner_name='李四'). Since the string literal evaluates to true (1) and the comparison owner_name='李四' is false (0), the whole expression evaluates to 0. MySQL stores this boolean 0 in owner_code, which explains the observed result.

Correct Syntax

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

Running the corrected statement updates both columns as intended.

Conclusion

When updating multiple columns in a single MySQL UPDATE statement, separate assignments with commas, not AND. Using AND creates a logical expression that can unintentionally set numeric columns to 0.