t-sql update statement with variable (replacing looping or cursor)

Try the following commonly know trick as "update with variable", it should work with both SQL server and sybase.

One thing I noticed recently is that sql server seems always execute the variable assignment first (regardless if you put variable update line before or after the field update statement). Sybase may have different behavior.


use master
go
declare @rowsnumber int
set @rowsnumber=0

select
    0 as RowsNumber
    ,number
    into #T
    from master..spt_values
    where type ='P'

select * from #T

update #T
    set @rowsnumber=@rowsnumber+1
        ,RowsNumber=@rowsnumber
    from #T

select * from #T

drop table #T