MySql - What happens when i update with 'and'

Question

I ran...

update TABLE set COLUMN1='XXX' and  COLUMN2='YYY' where ID=9999;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Correct Query:

update TABLE set COLUMN1='XXX' , COLUMN2='YYY' where ID=9999;

Why MYSQL didnt throw any error?

Answer 1

I just checked it and it looks like MySQL treats

'XXX' and COLUMN2='YYY'

like expression. So it compares values of COLUMN2 and 'YYY', gets boolean result and performs 'XXX' AND that result, so you probably get 0 in COLUMN1 in the end since 'XXX' is not DOUBLE/INTEGER/BOOLEAN. COLUMN2 will be left unchanged.

This way this query is not erroneous, it just does not what you expected.