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

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.

CodePudding user response：

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

CodePudding user response：

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.