quick question re number sequence

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

SQL Server 2005 Forums

Transact-SQL (2005)

quick question re number sequence

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-12 : 14:56:27

Just created a field called number (int)
can i run an update query to populate all the rows with a unique number sequence

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-12 : 15:10:05

Yes you can use the ROW_NUMBER() function for this.

But is your intention to maintain that column via code or are you planning on using the identity option?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-12 : 15:13:44

something like this?

declare @num int
set @num = 0

DECLARE @table TABLE (col1 int,Col2 int)
INSERT INTO @table
SELECT 1,null union all
SELECT 1,null union all
SELECT 1,null union all
SELECT 1,null

UPDATE @table
SET @num = col2 = @num+ 1
select * from @table

Jim

Everyday I learn something that somebody else already knew

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 15:18:18

Or like how Tara mentioned, using ROW_NUMBER()

DECLARE @table TABLE (col1 int,Col2 int)
INSERT INTO @table 
SELECT 1,null union all
SELECT 1,null union all
SELECT 1,null union all
SELECT 1,null 

UPDATE t
SET t.Col2 = t.seq
from (select row_number() over(order by col1) as seq ,* from @table) t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-13 : 11:01:11

It is better to have it as part of SELECT statement so that you dont need to update everytime data are added to the table

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-13 : 13:42:15

Agreed, so this is a one-time update only. After that, the application will need to handle incrementing the value (which is just silly as an identity can do it for you).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-13 : 14:22:35

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources