1

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.

user1191247
  • 10,808
  • 2
  • 22
  • 32
Tomas Novak
  • 13
  • 3
  • 2
    You need in recursive CTE - study [WITH clause (Common Table Expressions)](https://dev.mysql.com/doc/refman/8.0/en/with.html). – Akina Feb 22 '23 at 09:17
  • Its just too complex for me, thanks for your advice, Ill make it in VS... – Tomas Novak Feb 22 '23 at 11:44
  • 1
    It is simple. [Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055), #5 and #3 - and I'll show you that this is really simple. – Akina Feb 22 '23 at 12:36
  • OK @Akina, expected outcome is editing NodeValid column based on children NodeValid state as mentioned in question. In my case its more complicated than 0/1, but its driven by very simple rules I can modify later. Every layer of children has different rules so I would proceed every level separately. My problem is that I am not able to walk through results of "SELECT". Program should work like (I am sorry for my lack of mySQL syantaxes): NodeLevel 3 is solved by code in question: `UPDATE table SET NodeValid=1 WHERE NodeLevel=3 AND NodeValue > 2023-02-22;` – Tomas Novak Feb 22 '23 at 13:13
  • NodeLevel 2, 1 and 0 should be proceeded like: Get all parents of NodeLevel=2 `ArrayX = SELECT DISTINCT(Nodeparent) FROM table WHERE NodeLevel=2; ` For each ArrayX check all childrens NodeValid `If any ( SELECT NodeValid FROM table WHERE NodeParent= ArrayX ; ) = 0 then ArrayX NodeValid=0 ` Hope I am clear enough... – Tomas Novak Feb 22 '23 at 13:39
  • 1
    I get validated by my children. But only when I feed them. – Martin Feb 22 '23 at 14:01
  • If you like doing it by hand and are paid by the hour, go for it, but if you would rather right click and be done with it, use Workbench and never look back --> https://dev.mysql.com/downloads/workbench/ – easleyfixed Feb 22 '23 at 19:54

1 Answers1

0

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;
user1191247
  • 10,808
  • 2
  • 22
  • 32