MySQL validating parents by children

Question

I am programming in Visual Studio, but I need to perform validation on mySQL (v8.0.28) table and I guess it would be faster if it will be performed as procedure on server. Unfortunatelly, my MySQL programming skills are very limited.

This is equivalent of my table:

NodeID	NodeLevel	NodeParent	NodeValue	NodeValid
0	0	root	NULL	0
1	1	0	NULL	0
2	2	1	NULL	0
3	3	2	2023-03-03	1
4	0	root	NULL	0
5	1	4	NULL	0
6	2	5	NULL	0
7	3	6	2023-03-03	1
8	0	root	NULL	0
9	1	8	NULL	0
10	1	8	NULL	0

It works like Treeview nodes and I am able to validate state of bottom level nodes by this line:

UPDATE `bs`.`valitable` 
SET `NodeValid`='1' 
WHERE `NodeLevel`='3' AND `NodeValue` > '2023-02-22';

How I can walk through nodes where NodeLevel='2', because I need to check if all children are Valid = 1 (then Parent Valid = 1), otherwise parent Valid = 0.

I found some WHILE...DO, FOR loops, Cursors, but I am not able to write correct syntaxe to perform this action correctly. Or - is there other way how to do this?

I tried to do this in Visual Studio, but its too slow because it require to load entire table, proceed and save back. As the table grows, its unusable so I hope mySQL procedure could speed up the process, because it could run directly from server.

Answer 1

You should change your table so that you represent your NodeLevel 0 nodes with NodeParent NULL instead of root. This is required for the FK constraint from NodeParent to NodeID anyway, which you should have in place.

This approach uses a recursive cte to build a full list of leaf nodes (NodeLevel = 3) and all their ancestors. So based on your description, we can update all ancestors to valid if all related leaf nodes are valid (NodeValue > '2023-02-22'):

WITH RECURSIVE cte (AncestorID, LeafID) AS (

    SELECT NodeParent, NodeID
    FROM valitable WHERE NodeLevel = 3

    UNION ALL

    SELECT v.NodeParent, c.LeafID
    FROM valitable v
    JOIN cte c ON v.NodeID = c.AncestorID AND v.NodeParent IS NOT NULL

)
UPDATE valitable a
JOIN (
    SELECT cte.AncestorID, MIN(l.NodeValue > '2023-02-22') valid
    FROM cte
    JOIN valitable l ON cte.LeafID = l.NodeID
    GROUP BY cte.AncestorID
) j ON a.NodeID = j.AncestorID
SET a.NodeValid = j.valid;

For this to work you need to work as expected you need to run your initial update of the NodeLevel 3 nodes first.

This will not make any changes to nodes that have no relationship to a NodeLevel 3 node, as there is no explanation of the rules for these nodes.

---

Update

To set NodeValid = NULL where the node has no relation to a NodeLevel 3 node you can change the JOIN to LEFT JOIN in the update statement, but you need to make sure it is only updating where NodeLevel < 3, otherwise it will NULL all the NodeLevel 3 nodes, as they are not included as ancestors in the cte:

-- start with your initial update
UPDATE valitable
SET NodeValid = NodeValue > '2023-02-22' -- 1 if meets criterion, 0 if not
WHERE NodeLevel = 3;

WITH RECURSIVE cte (AncestorID, LeafID) AS (

    SELECT NodeParent, NodeID
    FROM valitable WHERE NodeLevel = 3

    UNION ALL

    SELECT v.NodeParent, c.LeafID
    FROM valitable v
    JOIN cte c ON v.NodeID = c.AncestorID AND v.NodeParent IS NOT NULL

)
UPDATE valitable a
LEFT JOIN (
    SELECT cte.AncestorID, MIN(l.NodeValue > '2023-02-22') valid
    FROM cte
    JOIN valitable l ON cte.LeafID = l.NodeID
    GROUP BY cte.AncestorID
) j ON a.NodeID = j.AncestorID
SET a.NodeValid = j.valid
WHERE a.NodeLevel < 3;