SQLite Forum

Assuming you're using fnidx to order your rows, you would be doing something like …

* for each row of the table
* let <code>c</code> be the the number of rows which have an fnidx less than the fnidx of that row
* set the fnrownum of that row to <code>c + 1</code>

This task will be far faster if you have an index on the fnidx column.

You can do this by iterating through the table in fnidx order in your favourite programming language, adding 1 to a counter.

You might be able to do something like

<code>UPDATE tnTEST AS thisrow SET fnrownum =
    (SELECT COUNT(*) FROM tnTEST AS others
        WHERE others.fnidx<thisrow.fnridx)+1</code>

I'm betting that someone can write a <code>WITH</code> statement to do it, too.