I have the following table (tnTEST) :- fnidx fntext fnrownum 10 aa null 11 bb null 12 cc null 13 dd null 14 ee null and would like to use UPDATE and ROW_COUNT to produce this :- fnidx fntext fnrownum 10 aa 1 11 bb 2 12 cc 3 13 dd 4 14 ee 5 How do I do this?
(2) By Larry Brasfield (LarryBrasfield) on 2021-01-28 17:47:46 in reply to 1.1 [source]
I cannot tell what your fnrownum column is supposed to mean. I can guess that its meaning may be well met by counting rows in your table with some ordering criterion (that you have not mentioned.) I hesitate to suggest a real solution because the problem is too ill-defined. However, this would at least produce the result you say you want with the table content you have shown:
UPDATE tnTEST set fnrownum = fnidx - 9;
Assuming you're using fnidx to order your rows, you would be doing something like …
- for each row of the table
cbe the the number of rows which have an fnidx less than the fnidx of that row
- set the fnrownum of that row to
c + 1
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
UPDATE tnTEST AS thisrow SET fnrownum =
(SELECT COUNT(*) FROM tnTEST AS others
I'm betting that someone can write a
WITH statement to do it, too.
Windows functions have
row_number() which seems to be what you want. See the
Once you have that, then you need to update the table by joining with that result (it's a
correlated subquery in fact, but that's the same idea). Below I assumed your order was
but just change the windowing to whatever order you actually want.
There may be a better way, and it may not be fast. But at least it seems to work :)
C:\Users\ddevienne>sqlite3 SQLite version 3.33.0 2020-08-14 13:23:32 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t(idx integer primary key, txt text, ord integer); sqlite> insert into t(idx, txt) values (10,'aa'), (11,'bb'), (12,'cc'), (13,'dd'), (14,'ee'); sqlite> .mode box sqlite> select * from t; ┌─────┬─────┬─────┐ │ idx │ txt │ ord │ ├─────┼─────┼─────┤ │ 10 │ aa │ │ │ 11 │ bb │ │ │ 12 │ cc │ │ │ 13 │ dd │ │ │ 14 │ ee │ │ └─────┴─────┴─────┘ sqlite> select idx, txt, row_number() over (order by idx) as win_ord from t order by idx; ┌─────┬─────┬─────────┐ │ idx │ txt │ win_ord │ ├─────┼─────┼─────────┤ │ 10 │ aa │ 1 │ │ 11 │ bb │ 2 │ │ 12 │ cc │ 3 │ │ 13 │ dd │ 4 │ │ 14 │ ee │ 5 │ └─────┴─────┴─────────┘ sqlite> with tt(idx, win_ord) as (select idx,row_number() over (order by idx) from t) ...> update t set ord = (select win_ord from tt where tt.idx = t.idx); sqlite> select * from t; ┌─────┬─────┬─────┐ │ idx │ txt │ ord │ ├─────┼─────┼─────┤ │ 10 │ aa │ 1 │ │ 11 │ bb │ 2 │ │ 12 │ cc │ 3 │ │ 13 │ dd │ 4 │ │ 14 │ ee │ 5 │ └─────┴─────┴─────┘ sqlite>
That's confusing - this seems to be a direct re-submit of the post in which this was already perfectly answered:
Was that answer not satisfactory? The question seems a direct repeat.
Indeed. Didn't notice that myself. Oh well, was a nice exercise for me anyway, to work it out on my own.
Well the answers are welcome, the more the merrier, I was just not understanding why the second post of the same question.
BTW - I've learned from your post that the window definition without a partition clause seems to work the same as a PARTITION BY 1 - I did not know that, so I for one am happy you had a go.
to work the same as a PARTITION BY 1
Is 1 a column position or simply a numeric scalar?
Looking at (and testing) it after seeing Dominique's usage, my understanding is that it's a scalar expression, one could achieve the same by saying:
(PARTITION BY 'Reindeer' ... )
It simply means the partitioning is based on the exact same value for all rows, much like GROUP BY and as opposed to the ORDER BY clause where a constant integer indicates the column index to order by.