Databases 3 min read

Why Using AND in MySQL UPDATE Sets a Field to 0 (and How to Fix It)

This article explains why an UPDATE statement that separates column assignments with AND fails in MySQL, how the misuse turns values into 0, and shows the correct comma‑separated syntax with examples and code snippets to reliably update multiple fields.

Java Interview Crash Guide

Sep 11, 2023

Why Using AND in MySQL UPDATE Sets a Field to 0 (and How to Fix It)

Several developers asked why an UPDATE statement in MySQL appears to run without changing a record.

Conclusion

When updating multiple columns in a single UPDATE, fields must be separated by commas, not the keyword AND.

Problem

The original statement used AND between assignments, causing unexpected results.

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

After execution the owner_code became 0 while owner_name stayed unchanged.

Analysis

MySQL documentation shows that the assignment list in UPDATE must be a comma‑separated list of col_name=value pairs.

Correct Syntax

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

Running this statement updates both columns as expected.

Why AND Produces 0

Using AND between assignments turns the expression into a logical operation; MySQL evaluates ('43212' AND owner_name='李四') as false, which is stored as 0.