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.
>Solution :
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.