MySQL: Set value in column based on other tables data (need optimize)

Question

I have two tables in a MySQL v8 database.

Table relations

child_id	parent_id
11	1
12	1
13	1
21	2
23	2

Table dates

id	date
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

I need to update disintegrated (0000-00-00 or NULL) dates in first table, basing on data from second and first table.

1. get id for row with 0000-00-00 or NULL in dates
2. get parent_id in relations for child_id which equals found id from dates
3. get date for id from dates which equals found parent_id
4. update date in dates by this found one for id (from first step)

Example:

1. found NULL date and for it dates.id is 12
2. for relations.child_id = 12 get value of relations.parent_id = 1
3. for dates.id = 1 get value of dates.date = 2023-01-01
4. for dates.id = 12 set value dates.date = 2023-01-01

I wrote something like this - attempt with subqueries:

UPDATE dates d1
SET d1.date = (SELECT d2.date 
               FROM 
                   (SELECT * FROM dates) d2 
               WHERE d2.id = (SELECT r.parent_id 
                              FROM relations r 
                              WHERE r.child_id = d2.id))
WHERE CAST(d1.date AS UNSIGNED) = 0;

Attempt with joins:

UPDATE dates d1
LEFT JOIN relations r ON (d1.id = r.child_id)
SET d1.date = (SELECT d2.date 
               FROM (SELECT * FROM dates) d2 
               WHERE d2.child_id = r.parent_id)
WHERE CAST(dates.date AS UNSIGNED) > 0

What I need

Both solutions work, but they are very heavy.

Is there any other, lighter and smoother, solution?

Answer 1

if there is only one step to the date you can use

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

fiddle