Execute an SQL UPDATE using GROUP BY and COUNT

I am working with SQL in an SQLite database. I have a table that looks something like this:

STORAGE
------------------------------
REC_ID   SEQ_NO       NAME
------------------------------
100       1          plastic jar
100       2          glass cup
100                  fiber rug
101       1          steel fork
101                  wool scarf
102       1          leather boots
102       2          paintbox
102       3          cast iron pan
102                  toolbox

Keep in mind that that this is a very small number of records compared to what I actually have in the table. What I need to do is update the table so that all the records that have a null value for SEQ_NO are set with the actual number they are supposed to be in sequence to the group of records with the same REC_ID.

Here is what I want the table to look like after the update:

STORAGE
------------------------------
REC_ID   SEQ_NO       NAME
------------------------------
100       1          plastic jar
100       2          glass cup
100       3          fiber rug
101       1          steel fork
101       2          wool scarf
102       1          leather boots
102       2          paintbox
102       3          cast iron pan
102       4          toolbox

so for example, the record with REC_ID 102 should have have SEQ_NO of 4, because it is the fourth record with the REC_ID 102.

If I do:

SELECT REC_ID, COUNT(*) FROM STORAGE GROUP BY REC_ID;

this returns all of the records by REC_ID and the number (count) of records matching each ID, which would also be the number I would want to assign to each of the records with a null SEQ_NO.

Now how would I go about actually updating all of these records with their count values?

Answers


this should work:

update storage set 
seq_no=(select count(*) from storage s2 where storage.rec_id=s2.rec_id)
where seq_no is null

Need Your Help

how to return guid from a class in the controller

c# asp.net-mvc-2

Is there a way I can return a Guid with the code below? For example, what I'm passing in to AddSchedule is a class. I want to return an Id from the class so I can so something with it in the contro...

How I can convert window coordinate into normalized device coordinates?

opengl math glut

I have my window cordinate, and I need to draw text here.