MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version()

8.0.36

create table mytable (id int, date date);
insert into mytable values (2, '2020-01-01');
insert into mytable values (1, '2021-01-01');
insert into mytable values (8, '2022-01-01');
update mytable t
inner join (select id, row_number() over(order by date, id) new_id from mytable) t1
  on t.id = t1.id
set t.id = t1.new_id

Rows matched: 3  Changed: 3  Warnings: 0

select * from mytable;

id
date

1
2020-01-01

2
2021-01-01

3
2022-01-01