Question 169975 in Mysql Question posted in
The official MySQL documentation can be found here.

Can I assign a col to an auto-updating col in MySQL safely?

my updated_at column is set to current timestame ON UPDATE.

If the column active was False and is changed to True, I also want to change activated_at to be the same value as updated_at.

will the query

UPDATE mytable
SET active=TRUE, activated_at=mytable.updated_at
WHERE active=FALSE;

use the new post-on update value of updated_at, or the old one, or is the behavior undefined?

Tags:

2

Answers


  1. 0

    Your Query is incorrect. You need a select query to get the updated_at value:

    UPDATE mytable
SET active=TRUE, activated_at=(select updated_at
 from mytable where id=<current id of the rec being updated> limit 1)
 WHERE id=<current id of the rec being updated>;
    Login or Signup to reply.
  2. 0

    I suppose a simple test will probably get the answer faster for you than asking.

    create table mytable(active bool,activated_at datetime,updated_at timestamp);
insert mytable values
(false,'2023-01-01 11:11:11',default),
(true,'2023-01-03 12:11:11',default),
(false,'2023-01-05 12:21:11',default);

select * from mytable;
+--------+---------------------+---------------------+
| active | activated_at        | updated_at          |
+--------+---------------------+---------------------+
|      0 | 2023-01-01 11:11:11 | 2023-05-17 01:45:11 |
|      1 | 2023-01-03 12:11:11 | 2023-05-17 01:45:11 |
|      0 | 2023-01-05 12:21:11 | 2023-05-17 01:45:11 |
+--------+---------------------+---------------------+

    Let’s try your query:

    UPDATE mytable
SET active=TRUE, activated_at=mytable.updated_at
WHERE active=FALSE;

select * from mytable;                                                   
+--------+---------------------+---------------------+
| active | activated_at        | updated_at          |
+--------+---------------------+---------------------+
|      1 | 2023-05-17 01:45:11 | 2023-05-17 01:47:28 |
|      1 | 2023-01-03 12:11:11 | 2023-05-17 01:45:11 |
|      1 | 2023-05-17 01:45:11 | 2023-05-17 01:47:28 |
+--------+---------------------+---------------------+

    As demonstrated above, the updated rows are using the OLD values before the timestamp auto-update kicks in. To circumvent that, we can simply set the value to the current datetime:

    -- Let's truncate/insert the table and do it all over again.
 select * from mytable;                                                   
+--------+---------------------+---------------------+
| active | activated_at        | updated_at          |
+--------+---------------------+---------------------+
|      0 | 2023-01-01 11:11:11 | 2023-05-17 01:54:31 |
|      1 | 2023-01-03 12:11:11 | 2023-05-17 01:54:31 |
|      0 | 2023-01-05 12:21:11 | 2023-05-17 01:54:31 |
+--------+---------------------+---------------------+

    Here is the slightly modified query:

    UPDATE mytable
SET active=TRUE, activated_at=current_timestamp()
WHERE active=FALSE;

 select * from mytable;
+--------+---------------------+---------------------+
| active | activated_at        | updated_at          |
+--------+---------------------+---------------------+
|      1 | 2023-05-17 01:56:11 | 2023-05-17 01:56:11 |
|      1 | 2023-01-03 12:11:11 | 2023-05-17 01:54:31 |
|      1 | 2023-05-17 01:56:11 | 2023-05-17 01:56:11 |
+--------+---------------------+---------------------+

    That’s it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search