On 11/1/2013 12:45 AM, Simon Slavin wrote:
Suppose I have a table which has a spare column called "currentOrder". Most of
the time this column doesn't matter but occasionally I need to update it to reflect the
order that the rows would appear if I did
SELECT rowid FROM myTable ORDER BY [order details]
The order clause is computationally expensive and I want to store the order it
produces for future use so I don't have to keep doing it. Is there a single
UPDATE command which will store 1 in the first row, 2 in the next, etc. ?
There is, but you probably won't like it.
update myTable set currentOrder = 1 +
(select count(*) from myTable t2 where [t2 row comes before myTable
row according to order details]);
It would likely be much faster to do it in the application code. Iterate
over the SELECT you've shown, and run
update myTable set currentOrder = :nextValue where rowid=:currentRow;
for each row.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users