MySQL - What Does It Mean To Have An AND Before WHERE In A Set Query?

Question

Recently, I discovered that a query I had modified about a year ago was silently failing (doing nothing at all).

I traced the problem to a syntax error. Instead of using a comma to set multiple fields I'd put AND by mistake. So instead of setting a second field as intended, it was setting no fields at all.

My original query was like this:

UPDATE `report` SET `confirm` = 2 WHERE `id` = 999

Modifying it like this is apparently perfectly valid syntax:

UPDATE `report` SET `confirm` = 2 AND `time_confirm` = UNIX_TIMESTAMP() WHERE `id` = 999

I know that I should use a comma (and did even then), and I should have tested even a small simple change like this (beyond the standard error handling), and I've now paid very dearly for that mistake. However, I'm still quite curious to understand why the query is valid and what it's actually doing.

According to the MySQL documentation, it expects a comma separated list of assignments at that location.

What does the query I put actually do and why is it allowed in the MySQL syntax without throwing any sort of error?

Answer 1

AND is also a Logical operator(Mysql Ref), so you the syntax should mean:

assign (2 AND time_confirm = UNIX_TIMESTAMP()) to confirm