update multiple columns, seperated by 'and' :What's happening?

[abduraooft]

Hi,

Just for curiosity, I've changed my queryupdate users set `act_date`=NOW(),`reg_date`=NOW() where id=334 to
update users set `act_date`=NOW() and `reg_date`=NOW() where id=334
I haven't got any syntax error, but the last column changed to 0000-00-00 00:00:00

Actually what happened there?

Thanks.

[Andrew Johnson]

You're supposed to use commas.

I'm surprised there was no error, apparently MySQL is taking the MSIE approach of "if you're close we'll guess as to what you wanted to do"

But ya, long story short: use commas

[ralph l mayo]

What's happening here is a type error rather than a syntax error, which MySQL doesn't report because for the most part it is not type safe.

The latter update evaluates to something like (Postgres casting syntax used here for clarity, doesn't actually work in MySQL):


UPDATE users
SET act_date = (NOW()::boolean AND reg_date = NOW())
WHERE id = 334;


NOW() as a boolean is true. Don't ask me why, but it is:

SELECT 1 FROM users WHERE NOW() => 1
SELECT 1 FROM users WHERE NOT NOW() => Empty set


reg_date = NOW() is not a type mismatch, but the = switched semantics. It's very unlikely to be true, so the end result of the expression is false and the 'and' variant is the same as:

UPDATE users SET act_date = 0 WHERE id = 334;


Had the result been true the column still would have been zeroed, but it would have raised a warning:

Warning | 1265 | Data truncated for column 'act_date' at row 1