Facebook Twitter Reddit LinkedIn

How to update a column with incrementing numbers

-- whole table (identity stlye)
declare @table1 table (id int, name varchar(50))
insert into @table1
select null, 'text1' union all
select null, 'text2' union all
select null, 'text3' union all
select null, 'text4'
select * from @table1
declare @inc int
set @inc = 0
UPDATE @table1 SET @inc = id = @inc + 1
select * from @table1
go

-- groups of data:
declare @table table (id int, diag int, count1 int, rank int)
insert into @table
select 1, 42, 75, null union all
select 1, 49, 50, null union all
select 1, 38, 22, null union all
select 2, 70, 48, null union all
select 2, 33, 27, null union all
select 2, 30, 12, null union all
select 2, 34, 5, null union all
select 2, 54, 3, null union all
select 3, 42, 75, null union all
select 3, 49, 50, null union all
select 3, 38, 22, null
declare @cnt int
set @cnt = 0
UPDATE t1
SET @cnt = rank =
case when
exists (select top 1 id from @table where id t1.count1)
then 1
else
@cnt + 1
end
from @table t1
select * from @table

Source: http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
© 2007-2019 All rights reserved.   Part of the somuch.com group of trusted web sites.   PO Box 351031, Palm Coast FL 32135-1031