SQL Update to ReOrder Column with Int Values

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 2000 Forums

SQL Server Development (2000)

SQL Update to ReOrder Column with Int Values

Author

Topic

cardnal0602
Starting Member

11 Posts

Posted - 2009-08-15 : 14:42:02

Hey

I have an int column used for sorting records. Over time, some records get deleted, making the sorting numbers increase non sequentially.

It would look like this:
1, 3, 5, 3, 2, 9, 8, 12, 19, 33, 55, etc...

and I want to rest them to look like
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11

Is there an easy way to do this?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-15 : 15:33:24

Is the column also your primary key?
Do other tables reference this column?

N 56°04'39.26"
E 12°55'05.63"

cardnal0602
Starting Member

11 Posts

Posted - 2009-08-16 : 21:56:39

quote:
Originally posted by Peso

Is the column also your primary key?
Do other tables reference this column?

Hey

This isn't a primary key and no other tables reference it.
Just a standard INT column that I use for sorting the output to the screen.

N 56°04'39.26"
E 12°55'05.63"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-17 : 01:05:47

[code] try like this

declare @i table (id int )
insert into @i
select 1 union all
select 3 union all
select 5 union all
select 3 union all
select 2 union all
select 9 union all
select 8 union all
select 12 union all
select 19 union all
select 33 union all
select 55

select * from @i

declare @k int
select @k = 0

update @i
set @k =id = @k + 1

select * from @i
[/code]

Happy Independence Day..............

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-08-18 : 09:08:24

Why do you care about gaps if it's used for sorting?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-19 : 03:55:58

Yes. If it is used for sorting purpose, no need to worry about the gaps. Order by col will work perfectly

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources