TSQL - auto increment in an UPDATE expression

Question

SQL Server 2005

I have a table containing the following: -

[order_id]     [index_1]
600020001      0
600020002      0
600020002      0
600020002      0
600020003      0
...

which must be updated to: -

[order_id]     [index_1]
600020001      1
600020002      1
600020002      2
600020002      3
600020003      1  

I am trying to write an UPDATE statement that populates the field index_1, as in the example above. I can achieve this using the CURSOR, but ideally would like to do it if possible.

For each new order_id, the numbering is overwritten. For each order_id line, the index_1 field is incremented by 1.

Is it possible to do this without a cursor?

Answer 1

CTE row_number() , . @T . @T , .

declare @T table ([order_id] int, [index_1] int)

insert into @T values
(600020001,      0),
(600020002,      0),
(600020002,      0),
(600020002,      0),
(600020003,      0)

;with cte as
(
  select index_1,
         row_number() over(partition by order_id order by (select 1)) as rn
  from @T       
)
update cte 
  set index_1 = rn

select *
from @T

:

order_id    index_1
----------- -----------
600020001   1
600020002   1
600020002   2
600020002   3
600020003   1