mysql update multiple columns with same now

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 gt  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  because of the 2 visible calls to now    I don t think that it can be update with different values  I think internally mysql calls now   just once per row or something similar   but I m not an expert  what do you think   Update  Second question was extracted here

User · Answer

Mysql isn t very clever  When you want to use the same timestamp in multiple update or insert queries  you need to declare a variable    When you use the now   function  the system will call the current timestamp every time you call it in another query

User · Answer

MySQL evaluates now   once per statement when the statement commences execution   So it is safe to have multiple visible now   calls per statement   select now    select now    sleep 10   now    select now     ---------------------    now                    ---------------------    2018-11-05 16 54 00    ---------------------  1 row in set  0 00 sec    --------------------- ----------- ---------------------    now                   sleep 10    now                    --------------------- ----------- ---------------------    2018-11-05 16 54 00           0   2018-11-05 16 54 00    --------------------- ----------- ---------------------  1 row in set  10 00 sec    ---------------------    now                    ---------------------    2018-11-05 16 54 10    ---------------------  1 row in set  0 00 sec

User · Answer

There are 2 ways to this   First  I would advice you declare now   as a variable before injecting it into the sql statement  Lets say   var x   now    mysql gt  UPDATE table SET last update  x  last monitor  x WHERE id 1    Logically if you want a different input for last monitor then you will add another variable like   var y   time    mysql gt  UPDATE table SET last update  x  last monitor  y WHERE id 1    This way you can use the variables as many times as you can  not only in mysql statements but also in the server-side scripting-language like PHP  you are using in your project  Remember these same variables can be inserted as inputs in a form on the front-end of the application  That makes the project dynamic and not static   Secondly if now   indicates time of update then using mysql you can decalre the property of the row as a timestamp  Every time a row is inserted or updated time is updated too

User · Answer

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

User · Answer

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 gt  update table set last update now   where id 1  mysql gt  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

User · Answer

Found a solution  mysql gt  UPDATE table SET last update now    last monitor last update WHERE id 1   I found this in MySQL Docs and after a few tests 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

User · Answer

You can put the following code on the default value of the timestamp column  CURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP  so on update the two columns take the same value

[mysql] mysql update multiple columns with same now()

Examples related to mysql

Examples related to sql-update