sql server - How to iterate an int column for each distinct value in SQL? -

i've been trying query update current database can't figure out how it. i've been trying cursors can't find way isolate each row , not set rows of distinct column same value.

here have in database:

reconumber   item abibaqc-01   1 abibaqc-01   1 abibaqc-01   1 abibaqc-02   1 abibaqc-03   1 abibaqc-03   1 

and become:

reconumber   item abibaqc-01   1 abibaqc-01   2 abibaqc-01   3 abibaqc-02   1 abibaqc-03   1 abibaqc-03   2 

like said, i've tried cursor missing make work properly.

declare @number int declare @reco nvarchar(10) declare @reco_old nvarchar(10)  declare db_cursor cursor select distinct reconumber workbook2014_test.dbo.reco  open db_cursor fetch next db_cursor @reco set @number = 1 set @reco_old = @reco  while @@fetch_status = 0 begin     update workbook2014_test.dbo.reco     set item = @number      reconumber = @reco      fetch next db_cursor @reco     if(@reco != @reco_old)         set @number = 1     else         set @number = @number + 1 end  close db_cursor deallocate db_cursor 

thanks in advance tips.

you can use row_number() function this:

;with cte (select *,row_number() over(partition reconumber order reconumber) upd_item                yourtable               ) update cte set item = upd_item 

the row_number() function assigns number each row. partition by is optional, used start numbering on each value in given field or group of fields, ie: if partition some_date each unique date value numbering start on @ 1. order by of course used define how counting should go, , required in row_number() function.

you can select * cte first observe new values before running update, may have other field values you'd prefer order by.