MySQL 8.0

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

CREATE TABLE dates
    (`id` int, `date` varchar(10))
;
    
INSERT INTO dates
    (`id`, `date`)
VALUES
    (1, '2023-01-01'),
    (11, '2023-05-15'),
    (12, NULL),
    (13, '0000-00-00'),
    (2, '2023-07-01'),
    (21, '2023-07-01'),
    (23, '2023-07-01')
;

Records: 7  Duplicates: 0  Warnings: 0


CREATE TABLE relations
    (`child_id` int, `parent_id` int)
;
    
INSERT INTO relations
    (`child_id`, `parent_id`)
VALUES
    (11, 1),
    (12, 1),
    (13, 1),
    (21, 2),
    (23, 2)
;

Records: 5  Duplicates: 0  Warnings: 0


UPDATE dates d1
SET d1.`date` = 
(SELECT d2.`date` FROM (SELECT * FROM dates) d2 JOIN relations r ON r.`parent_id`  = d2.id 
  AND r.Child_id = d1.id) 
  WHERE d1.`date` IS NULL OR d1.`date` = '0000-00-00'

Rows matched: 2  Changed: 2  Warnings: 0


SELECT * FROM dates


id
date


1
2023-01-01

11
2023-05-15

12
2023-01-01

13
2023-01-01

2
2023-07-01

21
2023-07-01

23
2023-07-01

id	date
1	2023-01-01
11	2023-05-15
12	2023-01-01
13	2023-01-01
2	2023-07-01
21	2023-07-01
23	2023-07-01