SQLite Forum



(1.1) By pcurtis on 2021-01-28 12:28:30 edited from 1.0 [link] [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 Larry Brasfield (LarryBrasfield) on 2021-01-28 17:47:46 in reply to 1.1 [link] [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;

(3) By Simon Slavin (slavin) on 2021-01-29 01:51:45 in reply to 1.1 [link] [source]

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

  • for each row of the table
  • let c be 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 WHERE others.fnidx<thisrow.fnridx)+1

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

(4) By ddevienne on 2021-01-29 09:29:12 in reply to 1.1 [link] [source]

I agree with Larry that your problem is ill-defined, and Simon's answer is as good as any,
but given that I'm a novice at CTEs and Window Funtions, I thought I'd give it a try, just for fun.

Windows functions have row_number() which seems to be what you want. See the select below.
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 idx,
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 :)

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   │

(5) By Ryan Smith (cuz) on 2021-01-29 10:10:55 in reply to 1.1 [link] [source]

That's confusing - this seems to be a direct re-submit of the post in which this was already perfectly answered:
Original Post

Was that answer not satisfactory? The question seems a direct repeat.

(6) By ddevienne on 2021-01-29 10:27:28 in reply to 5 [source]

Indeed. Didn't notice that myself. Oh well, was a nice exercise for me anyway, to work it out on my own.

(7) By Ryan Smith (cuz) on 2021-01-29 10:39:22 in reply to 6 [link] [source]

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.

(8) By anonymous on 2021-01-29 16:47:48 in reply to 7 [link] [source]

to work the same as a PARTITION BY 1

Is 1 a column position or simply a numeric scalar?

(9) By Ryan Smith (cuz) on 2021-01-29 19:11:36 in reply to 8 [link] [source]

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.