Parent-child dependend update

Databases
1

The background:
i’ve two tables, one for the parent, the other for the children:

parent
id - active
1 - 1
2 - 1
3 - 0
etc

children
id - parent_id - active
1 - 1 - 1
2 - 1 - 1
3 - 1 - 0
4 - 1 - 1
5 - 2 - 0
6 - 2 - 0
7 - 2 - 1
8 - 3 - 0
9 - 3 - 0

If the active flag of the parent table shows 1, item will be displayed, if it shows 0 it won’t.
The parent active flag is set to 1 if at least 1 of the children’s active flag of the according group (parent_id) is 1 and it’s set to 0 if all children’s active flag shows 0.

any hints on how would the query look like to get the parent active flag updated according to the children active flags?
Whatever i tried so far failed…

2

update parent p left join (select parent, sum(active) summer from children group by parent) c on p.id = c.parent
set p.active = if(c.summer, 1, 0)

3

sorry for the delay in answering, Philip.
it works as desired. thank you.

4

holy moly, i thought i can get it handled but failed (again). i feel like an idiot :headbang:
When extending the previous setup by a lookup table:

parent
id - active
1 - 1
2 - 1
3 - 0
etc

lookup
parent_id - children_id
1 - 1
1 - 2
1 - 3
1 - 4
2 - 5
2 - 6
2 - 7
3 - 8
3 - 9

children
id - active
1 - 1
2 - 1
3 - 0
4 - 1
5 - 0
6 - 0
7 - 1
8 - 0
9 - 0

Same requrements:
If the active flag of the parent table shows 1, item will be displayed, if it shows 0 it won’t.
The parent active flag is set to 1 if at least 1 of the children’s active flag of the according group (parent_id) is 1 and it’s set to 0 if all children’s active flag shows 0.