Oops, didn't concentrate, that query should best be:
WITH NewOrder(nid,norder) AS (
SELECT F1.id, (SELECT COUNT(*) * 100 FROM fruit AS F2 WHERE
F2.SortOrder < F1.SortOrder)
FROM fruit AS F1
ORDER BY F1.id
-- This last ORDER BY is important as it forces the above correlated
subquery
-- to not recompute and causes a temp index on id.
)
UPDATE fruit SET SortOrder = (SELECT norder FROM NewOrder WHERE nid =
fruit.id);
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
