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?
You are asking about row-counting but the intended result you posted looks a lot more like row-numbering. There is a way to do it using row-counting, but it is convoluted involving self-referencing joins.
Assuming it's a misprint and you meant row-numbering, this would do it:
WITH RC(rci,rcn) AS ( SELECT fnidx, ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY fnidx ASC) FROM tnTEST ) UPDATE tnTEST SET fnrownum = (SELECT rcn FROM RC WHERE rci=fnidx) ;
Note: I typed this from my imagination, so not tested and might have some syntax fault that needs adjusting, but I'm pretty sure the idea is sound.
For completeness, here is the version that will work using COUNTs rather than the ROW_NUMBER window function:
WITH RC(rci,rcn) AS ( SELECT t1.fnidx, COUNT(*) FROM tnTEST AS t1 JOIN tnTEST AS t2 ON t2.fnidx <= t1.fnidx GROUP BY t1.fnidx ) UPDATE tnTEST SET fnrownum = (SELECT rcn FROM RC WHERE rci=fnidx) ;
The base advantage here is that it will work on all SQL engines, including older SQLite versions (since it does not need WINDOW functions), BUT it will be significantly slower for any decently sized dataset.
(4.1) By Keith Medcalf (kmedcalf) on 2021-01-28 13:52:46 edited from 4.0 in reply to 3 [link] [source]
These could of course also use UPDATE FROM syntax rather than a CTE ...
UPDATE tnTEST SET fnrownum = rcn FROM ( select fnidx as rci, row_number() over (partition by 1 order by fnidx) as rcn from tnTEST ) WHERE rci == fnidx ;
Of course, thanks for mentioning.
The UPDATE ... FROM is quite a new addition to SQLite so you will have to have a newer SQLite version (3.33+), but it works great. To illustrate what Keith suggested:
WITH RC(rci,rcn) AS ( SELECT fnidx, ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY fnidx ASC) FROM tnTEST ) UPDATE tnTEST SET fnrownum = rcn FROM RC WHERE rci = fnidx ; -- OR, if you prefer sub-queries in stead of WITH queries -- UPDATE tnTEST SET fnrownum = rcn FROM (SELECT fnidx AS rci, ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY fnidx ASC) AS rcn FROM tnTEST ) AS RC WHERE rci = fnidx ;
Note: If you use the same names in the source/sub query it will be ambiguous and you will need to fully qualify the field names.
EDIT: Oops, I added the above before seeing Keith's latest update with examples, so apologies for seeming repetitive/superfluous.