Bart Smissaert wrote: > ID PARENT_ID FOLDER RANK > --------------------------------------------------------------- > 1 0 Main 000000001 > 2 1 CC 000000001-0000000002 > 3 1 BB 000000001-0000000003 > 4 1 AA 000000001-0000000004 > 5 2 B 000000001-0000000002-0000000005 > 6 2 A 000000001-0000000002-0000000006 > > What SQL should I use to update the field RANK if the first row is known to > be 0000000001, but all the next rows are null? I tried with a non-recursive > query, > but couldn't work it out.
You want to append an entry's rank to its parent's rank, but only for entries whose rank is still empty, and whose parent has a rank: UPDATE MyTable SET Rank = (SELECT Rank FROM MyTable AS Parent WHERE MyTable.Parent_ID = Parent.ID ) || printf('-%09d', ID) WHERE Rank IS NULL AND Parent_ID IN (SELECT ID FROM MyTable WHERE Rank IS NOT NULL); Repeat until no empty rows are left. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users