Update consecutive dates for a certain amount of records

I have a MySQL 5.7 table.

There are 9000 records that I need to set consecutive dates to.

The first 300 records will have 2025-01-02.

The next 300 records will have 2025-01-03.

And so on until all 9000 have been updated, up until the final date of 2025-01-31.

Is there a way to do this with a single query?

I don't want to do this 30 times:

UPDATE table SET date = '2025-01-02' ORDER RAND() LIMIT 300;

This is what I tried:

SET @row_num = 0;
SET @start_date = '2025-01-02';

UPDATE `DISTRIBUTION`
  JOIN (
    SELECT 
    `email`, 
    @row_num := @row_num + 1 AS row_num
FROM `DISTRIBUTION`
) AS numbered_rows ON `DISTRIBUTION`.`email` = 
numbered_rows.`email`
SET `DISTRIBUTION`.`date_column` = DATE_ADD(@start_date, 
INTERVAL FLOOR((row_num - 1) / 300) DAY)
WHERE `LIST` = 'list1' AND `DOMAIN` = 'GM';

But upon trying that, it was setting the records to dates out in the 2040s and back to 1930.

I can't see any reason why your query isn't working, it works for me. But you can simplify it by leaving out the JOIN, and just using @row_num directly.

set @row_num = -1;
set @start_date = '2025-01-02';

update distribution 
set date_column = date_add(@start_date, interval floor((@row_num := @row_num + 1) / 300) day) 
where list = 'list1' AND domain = 'GM'
order by email;

You have to initialize @row_num to -1 because the calculation uses the value after incrementing it.

DEMO

In the linked demo, my table is smaller so I used groups of 30 instead of 300, but the principle is the same.