MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


10.3.22-MariaDB


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;
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.No' at line 7

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/