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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
