MySQL – While Exists from a snippet throws error

I was reading through this page https://www.sqlteam.com/articles/more-trees-hierarchies-in-sql (great article on managing hierarchical data in SQL) and following along in MySQL and was trying to run this SQL snippet.

WHILE EXISTS (SELECT * FROM Tree WHERE Depth Is Null) 
UPDATE T SET T.depth = P.Depth + 1, 
T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + '/' 
FROM Tree AS T 
INNER JOIN Tree AS P ON (T.ParentNode=P.Node) 
WHERE P.Depth>=0 
AND P.Lineage Is Not Null 
AND T.Depth Is Null

Except I get the following error:

You have an error in your SQL syntax; it seems the error is around: ‘WHILE EXISTS ( SELECT * FROM Tree WHERE Depth Is Null ) UPDATE’ at line 1

I’ve tried adding in BEGIN, END, END WHILE etc from suggestions in While exists in mysql but still not working.

You can try and run the above snippet in an SQL validator (https://www.eversql.com/sql-syntax-check-validator/ is a good free online one) and see the error.

I’m looking for suggestions on why the above snippet doesn’t work from the blog / what can be changed so the same result is achieved.

Update – Adding in more data

Thanks @danblack for the suggestion to add more data to make it a verifiable example.

My current table

Node    ParentNode  EmployeeID  Depth   Lineage
100     NULL        1001        0       /
101     100         1002        NULL    NULL
102     101         1003        NULL    NULL
103     102         1004        NULL    NULL
104     102         1005        NULL    NULL
105     102         1006        NULL    NULL

What it should look like after the snippet is run

Node    ParentNode  EmployeeID  Depth   Lineage
100     NULL        1001        0       /
101     100         1002        1       /100/
102     101         1003        2       /100/101/
103     102         1004        3       /100/101/102/
104     102         1005        3       /100/101/102/
105     102         1006        3       /100/101/102/

@danblack pointed out that the snippet is Microsoft SQL, not MySQL and should use a recursive CTE to do this. This is my attempt but I still can’t figure it out.

;WITH user_count
AS
(
SELECT * FROM Tree WHERE Depth Is Null AS null_users
UNION ALL
WHILE EXISTS () 
UPDATE T SET T.depth = P.Depth + 1, 
T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + '/' 
FROM Tree AS T 
INNER JOIN Tree AS P ON (T.ParentNode=P.Node) 
WHERE P.Depth>=0 
AND P.Lineage Is Not Null 
AND T.Depth Is Null
)

Answer

In MySQL-8.0:

select version();

| version() |
| :-------- |
| 8.0.13    |

CREATE TABLE tree (
  `Node` VARCHAR(3),
  `ParentNode` VARCHAR(3),
  `EmployeeID` INTEGER,
  `Depth` INTEGER,
  `Lineage` VARCHAR(16)
);

✓

INSERT INTO tree
  (`Node`, `ParentNode`, `EmployeeID`, `Depth`, `Lineage`)
VALUES
  ('100', NULL, '1001', 0, '/'),
  ('101', '100', '1002', NULL, NULL),
  ('102', '101', '1003', NULL, NULL),
  ('103', '102', '1004', NULL, NULL),
  ('104', '102', '1005', NULL, NULL),
  ('105', '102', '1006', NULL, NULL);

✓

WITH RECURSIVE prev AS (
SELECT * FROM tree WHERE ParentNode IS NULL
UNION
SELECT t.Node,t.ParentNode,t.EmployeeID,p.Depth + 1 as Depth, CONCAT(p.Lineage, t.ParentNode, '/')
FROM tree t
JOIN prev p ON t.ParentNode = p.Node
)
SELECT * FROM prev;

Node | ParentNode | EmployeeID | Depth | Lineage      
:--- | :--------- | ---------: | ----: | :------------
100  | null       |       1001 |     0 | /            
101  | 100        |       1002 |     1 | /100/        
102  | 101        |       1003 |     2 | /100/101/    
103  | 102        |       1004 |     3 | /100/101/102/
104  | 102        |       1005 |     3 | /100/101/102/
105  | 102        |       1006 |     3 | /100/101/102/

WITH RECURSIVE prev AS (
SELECT * FROM tree WHERE ParentNode IS NULL
UNION
SELECT t.Node,t.ParentNode,t.EmployeeID,p.Depth + 1 as Depth, CONCAT(p.Lineage, t.ParentNode, '/')
FROM prev p
JOIN tree t ON t.ParentNode = p.Node
)
UPDATE tree t, prev p
SET t.Depth=p.Depth, t.Lineage=p.Lineage
WHERE t.Node=p.Node;

✓

SELECT * FROM tree

Node | ParentNode | EmployeeID | Depth | Lineage      
:--- | :--------- | ---------: | ----: | :------------
100  | null       |       1001 |     0 | /            
101  | 100        |       1002 |     1 | /100/        
102  | 101        |       1003 |     2 | /100/101/    
103  | 102        |       1004 |     3 | /100/101/102/
104  | 102        |       1005 |     3 | /100/101/102/
105  | 102        |       1006 |     3 | /100/101/102/

mysql8.0 db<>fiddle here

MariaDB does not yet supported UPDATE in CTEs

However it can be performed with a temporary table like:

CREATE TEMPORARY TABLE newtree AS
WITH RECURSIVE prev AS (
SELECT * FROM tree WHERE ParentNode IS NULL
UNION
SELECT t.Node,t.ParentNode,t.EmployeeID,p.Depth + 1 as Depth, CONCAT(p.Lineage, t.ParentNode, '/')
FROM tree t JOIN prev p ON t.ParentNode = p.Node
)
SELECT Node,Depth,Lineage FROM prev;

✓

UPDATE tree t, newtree p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.Node;

✓

SELECT * from tree

Node | ParentNode | EmployeeID | Depth | Lineage      
:--- | :--------- | ---------: | ----: | :------------
100  | null       |       1001 |     0 | /            
101  | 100        |       1002 |     1 | /100/        
102  | 101        |       1003 |     2 | /100/101/    
103  | 102        |       1004 |     3 | /100/101/102/
104  | 102        |       1005 |     3 | /100/101/102/
105  | 102        |       1006 |     3 | /100/101/102/

MariaDB-10.3 db<>fiddle here