clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805502 fiddles created (40747 in the last week).

select version();
version()
10.3.22-MariaDB
 hidden batch(es)


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


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);
 hidden batch(es)


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 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/
 hidden batch(es)


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
 hidden batch(es)