How to change the data in a column by the largest value?

Question

MySQL

I'm learning sql, I can't complete the task.
I have a table:

|id                    | quarter              | latest_vizit_date   |
=====================================================================
|1                     | 1                    | 2019-03-28          |
|1                     | 2                    | 2021-05-19          |
|1                     | 3                    | 2018-09-25          |
|1                     | 4                    | 2018-12-27          |
|2                     | 1                    | 2021-03-18          |
|2                     | 2                    | 2021-06-03          |
|2                     | 3                    | 2020-09-03          |
|2                     | 4                    | 2020-12-10          |

I need to replace all dates in the latest_vizit_date column with the latest (highest, latest). The output should be the following:

|id                    | quarter              | latest_vizit_date   |
=====================================================================
|1                     | 1                    | 2021-05-19          |
|1                     | 2                    | 2021-05-19          |
|1                     | 3                    | 2021-05-19          |
|1                     | 4                    | 2021-05-19          |
|2                     | 1                    | 2021-06-03          |
|2                     | 2                    | 2021-06-03          |
|2                     | 3                    | 2021-06-03          |
|2                     | 4                    | 2021-06-03          |

I have a hunch that it should be something like this:

update mytable
set latest_vizit_date = @max_date

But @max_dateI don't know how to determine. I tried this: @max_date := max(latest_vizit_date)- it is searched for the entire column, but I need it to take into account a separate id and within the quarters from 1 to 4.

Answer 1

update mytable, (
  select id, max(last_vizit_date) as max_date
  from mytable
  group by id
) x set
  mytable.last_vizit_date = x.max_date
where x.id = mytable.id