mysql update multiple columns

Question

I need to update 2 datetime columns, and I need them to be exactly the same, using mysql version 4.1.20. I'm using this query:

mysql> update table set last_update=now(), last_monitor=now() where id=1;

It is safe or there is a chance that the columns are update with different time, beacause of the 2 visible calls to now() ?
I don't think that it can be update with different values (I think internaly mysql calls now() just once per row or something similar) , but I'm not an expert, what do you think ?

Second question why this query doesn't update the columns:

mysql> update table set last_update=last_monitor=now() where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Thank you.

Answer 1

If you really need to be sure that now() has the same value you can run two queries (that will answer to your second question too, in that case you are asking to update last_monitor = to last_update but last_update hasn't been updated yet)

you could do something like:

mysql> update table set last_update=now() where id=1; mysql> update table set last_monitor = last_update where id=1;

anyway I think that mysql is clever enough to ask for "now()" only once per query.

Answer 2

You can store the value of a now() in a variable before running the update query and then use that variable to update both the fields last_update and last_monitor.

This will ensure the now() is executed only once and same value is updated on both columns you need.

Answer 3

Found a solution:

mysql> update table set last_update=now(), last_monitor=last_update where id=1;

I found this in mySql documentation and after a few test it works. the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

On the second question, don't have any answers, did find nothing on mysql docs about this.