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

Update the column Date` with the date less than two months using MySQL

This is my table MySQL 5.5.62 host version

uID Next_control Next_control_2 Date_alert
45265 2023-09-07 2023-07-07 NULL
45265 2022-12-16 2022-10-16 NULL
45265 2019-04-19 2019-02-19 NULL
45265 2016-09-18 2016-07-18 NULL
45265 2014-11-23 2014-09-23 NULL
45265 2009-08-11 2009-06-11 NULL

I need to update the column Date_alert with the date less than two months compared to the column Next_control only for most recent row for uID 45265

I have tried

UPDATE `dotable` q JOIN
(SELECT uID, MAX(Next_control) dt FROM `dotable` GROUP BY uID) t
SET q.Date_alert = DATE_SUB( t.dt, INTERVAL 2 MONTH ) 
WHERE q.uID = t.uID;

But the column Date_alert it is also updated for older rows for uID 45265:

uID Next_control Next_control_2 Date_alert
45265 2023-09-07 2023-07-07 2023-07-07
45265 2022-12-16 2022-10-16 2023-07-07
45265 2019-04-19 2019-02-19 2023-07-07
45265 2016-09-18 2016-07-18 2023-07-07
45265 2014-11-23 2014-09-23 2023-07-07
45265 2009-08-11 2009-06-11 2023-07-07

Expected Output:

uID Next_control Next_control_2 Date_alert
45265 2023-09-07 2023-07-07 2023-07-07
45265 2022-12-16 2022-10-16 NULL
45265 2019-04-19 2019-02-19 NULL
45265 2016-09-18 2016-07-18 NULL
45265 2014-11-23 2014-09-23 NULL
45265 2009-08-11 2009-06-11 NULL

Any help really appreciated.

Tags:

2

Answers


  1. 0

    I need to update the column Date_alert with the date less than two months compared to the column Next_control only for most recent row for uID 45265

    UPDATE dotable
SET Date_alert = dt - INTERVAL 2 MONTH
WHERE uID = 45265
ORDER BY dt DESC LIMIT 1
    Login or Signup to reply.
  2. 0

    Yet one more option is to add a condition in your WHERE clause, in which you check for the matching "Next_control" value.

    UPDATE `dotable` q JOIN
(SELECT uID, MAX(Next_control) dt FROM `dotable` GROUP BY uID) t
SET q.Date_alert = DATE_SUB( t.dt, INTERVAL 2 MONTH ) 
WHERE q.uID = t.uID AND q.Next_control = t.dt;

    Check the demo here.

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

Back To Top
Search