Sequential numbers in column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sequential numbers in column

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-10-25 : 05:35:17

Hi

I have a table with 13,000 rows, in one column called Prioirty each row has a value of 1.

Is it possible to use SQL to replace all of these '1' values with a sequential list. Example 1,2,3,4,5,6,7....all the way to 13,000?

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-10-25 : 07:56:16

Declare @Tem table (Id int,Name char(2))
Insert @Tem

Select 1,'a'
union all Select 1,'b'
union all Select 1,'c'
union all Select 1,'d'
union all Select 1,'e'

Declare @Tem1 Table (Name char(2),Rn int)
Insert @Tem1
Select Name,Rn=Row_Number() Over(Order By Name) From @Tem

Update t
Set Id=Rn
From @Tem t
inner join @Tem1 as t1
on t.Name = t1.Name

Select * From @Tem

veeranjaneyulu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 08:32:07

Why an unnecessary join?
keep it simple


UPDATE t
SET ID = Seq
FROM (
      SELECT ROW_NUMBER() OVER (ORDER BY Name) AS Seq,ID
      FROM Table
      )t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-28 : 05:48:59

Another simple method is


declare @i int
set @i=-1

update your_table set Prioirty =@i+1, @i=@i+1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources