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

[georgeacuster8937]

Hi there,

This is my table MySQL 5.5.62 host version

For example I have extract the uID number 45265

+-------+--------------+----------------+------------+
| 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 (it’s any example… my table contains other codes uID )

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 |
+-------+--------------+----------------+------------+

I need this output. Any help really appreciated.

+-------+--------------+----------------+------------+
| 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       |
+-------+--------------+----------------+------------+
6 rows in set (0.17 sec)

[m_hutley]

What’s the key on this table? Why do you have multiple rows for a single uid?

Anyway…

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;

Look closer at your WHERE condition, and think about why it’s updating every row for the user, and not just the latest… (Hint: You’re missing an AND…)

[georgeacuster8937]

Thanks for suggestion… You have right…

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;