SQLite Forum

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.