Update SQL with consecutive numbering within a group

Question

I found the technique to update rows of a table with consecutive numbers here:

The gist of the technique is to use (T-SQL):

update myTable
SET @myvar = myField = @myVar + 1

which is awesome and works very well.

Can this technique be extended to number different groups of records, with each group starting with 1 ? e.g.

There's [an answer you want](http://stackoverflow.com/a/1168075/11683) in the question you link to. See the last **EDIT** in it. — GSerg, Dec 18 '11 at 11:35
It's generally better, if you can generate the sequence after the fact, to always generate it during select rather than storing the sequences. In that way, you don't have to perform maintenance tasks (such as closing gaps during deletes, or reordering values due to updates) — Damien_The_Unbeliever, Dec 18 '11 at 13:54
possible duplicate of [Update SQL with consecutive numbering](http://stackoverflow.com/questions/1167885/update-sql-with-consecutive-numbering) — gbn, Dec 18 '11 at 15:25

score -1 · Answer 1 · answered Jan 02 '12 at 16:25

-1

You can simply add a WHERE Clause to your UPDATE statement. see http://dev.mysql.com/doc/refman/5.0/en/update.html for reference.

Basically you do something like:

update myTable
SET @myvar = myField = @myVar + 1
WHERE myCategory = 'CAT'

answered Jan 02 '12 at 16:25

simonecampora

No, he wants every different group (category) to get its own sequence. – ypercubeᵀᴹ Jan 02 '12 at 16:35
Then you need to loop over each category and re-initialize the counter at every loop-cycle. – simonecampora Jan 02 '12 at 16:40
See the link in the comments (by GSerg) that does it in one query. – ypercubeᵀᴹ Jan 02 '12 at 16:43
And the OP says he is using TSQL. Your link is for MySQL. – ypercubeᵀᴹ Jan 02 '12 at 16:43
my bad: indeed GSerge solution is the one. I have been answering for mySQL instead. – simonecampora Jan 02 '12 at 16:50

1 Answers1