MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE my_table (
    `id` INT PRIMARY KEY,
    `day` TINYINT NOT NULL,
    `date` DATE,
    INDEX idx_date_day(date, day)
);
INSERT INTO my_table VALUES
    ( 1, 1, NULL),
    ( 2, 2, NULL),
    ( 3, 3, NULL),
    ( 4, 4, NULL),
    ( 5, 5, NULL),
    ( 6, 6, NULL),
    ( 7, 7, NULL),
    ( 8, 3, '2024-05-29'),
    ( 9, 5, '2024-05-31'),
    (10, 1, '2024-06-17'),
    (11, 6, '2024-07-27'),
    (12, 6, '2024-08-10'),
    (13, 7, '2024-08-25'),
    (14, 5, '2024-09-06'),
    (15, 5, '2024-09-27'),
    (16, 7, '2024-09-29'),
    (17, 5, '2024-10-11'),
    (18, 6, '2024-10-19'),
    (19, 1, '2024-10-21');
ANALYZE TABLE my_table;
SELECT * FROM my_table;

Records: 19  Duplicates: 0  Warnings: 0




Table
Op
Msg_type
Msg_text


fiddle.my_table
analyze
status
OK




id
day
date


1
1
null

2
2
null

3
3
null

4
4
null

5
5
null

6
6
null

7
7
null

8
3
2024-05-29

9
5
2024-05-31

10
1
2024-06-17

11
6
2024-07-27

12
6
2024-08-10

13
7
2024-08-25

14
5
2024-09-06

15
5
2024-09-27

16
7
2024-09-29

17
5
2024-10-11

18
6
2024-10-19

19
1
2024-10-21


SELECT
    COALESCE(t2.id, t1.id) AS id,
    t1.day,
    t2.date
FROM my_table t1
LEFT JOIN my_table t2 ON t1.day = t2.day AND t2.date = '2024-05-29'
WHERE t1.date IS NULL;


id
day
date


1
1
null

2
2
null

8
3
2024-05-29

4
4
null

5
5
null

6
6
null

7
7
null


SELECT
    COALESCE(t2.id, t1.id) AS id,
    t1.day,
    t2.date
FROM (
    SELECT id, day, '2024-05-29' - INTERVAL (WEEKDAY('2024-05-29') + 1 - t.day) DAY AS date
    FROM my_table t
    WHERE t.date IS NULL
) AS t1
LEFT JOIN my_table t2 ON t1.day = t2.day AND t1.date = t2.date;


id
day
date


1
1
null

2
2
null

8
3
2024-05-29

4
4
null

9
5
2024-05-31

6
6
null

7
7
null
id	day	date
1	1	null
2	2	null
8	3	2024-05-29
4	4	null
5	5	null
6	6	null
7	7	null