UPDATE ROW_COUNT

(1) By pcurtis on 2021-01-28 11:56:57 [source]

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 Ryan Smith (cuz) on 2021-01-28 13:27:30 in reply to 1 [link] [source]

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.

(3) By Ryan Smith (cuz) on 2021-01-28 13:36:44 in reply to 1 [link] [source]

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
;

(5.1) By Ryan Smith (cuz) on 2021-01-28 14:08:24 edited from 5.0 in reply to 4.0 [link] [source]

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.