I want to UPDATE a field of one table with data of rows of the same table.
For example to calculate the depth of a node in a tree
update nodes as s set s.depth=
(select f.depth+1
from nodes as f
where f.id=s.father_id)
where s.depth is null;
I could do this in a ugly way:
create view nodes_father as select * from nodes;
update nodes set depth=
(select nodes_father.depth+1
from nodes_father
where nodes_father.id=nodes.father_id)
where s.depth is null;
But I want to do this without creating a view.
The complete example need initialization:
update nodes set depth=0
where father_id is null;
update nodes set depth=null
where father_id is not null;
and a repeat execution of the first update until the next statment returns
no rows:
select *
from nodes
where depth is null
Thanks. I'm sorry about my poor English.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
