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

Reply via email to