Perhaps I can solve this via the use of a sub-query.
Problem is I have a single table that I would like to do a self-join on and within an update query. But that does not appear to be supported in mysql. There must be a workaround.
a) some direction is what I am asking for, the the entire answer or someone to write it for me
b) MySQL Server version: 4.1.14-standard
In an effort to be brief I am trying to implement something similar to what is discussed here:
http://www.sqlteam.com/item.asp?ItemID=8866
Starting with a table like this:
Node ParentNode EmployeeID Depth Lineage
100 NULL 1001 NULL NULL
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
Using code similar to this (and by the way I am using Perl dbi::mysql):
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
Creating an updated table like this:
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/
Any suggestions on approach would be appreciated.
/mike