UPDATES Consecutives - 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

UPDATES Consecutives

Author

Topic

luarit
Starting Member

6 Posts

Posted - 2012-01-09 : 14:02:46

Hello All,

I have a question about Update statements.

If I have a table called table1

Id Name
1 A
2 B
3 C
4 D
5 E
6 F

And I wan to change it to this one

Id Name
2 A
3 B
4 C
5 D
6 E
7 F

Of course my table is more complicated, my idea was

update table1 set Id=2 where Id=1;
update table1 set Id=3 where Id=2;
update table1 set Id=4 where Id=3;
update table1 set Id=5 where Id=4;
update table1 set Id=6 where Id=5;
update table1 set Id=7 where Id=6;

My problem is that the first Update will do 2 rows with Id=2, the second one, will do 3 rows with Id=3,...

Is there any way to do that correctly?

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-09 : 14:40:16

I assume you are updating a key...which is a WHOLE other animal

but....

UPDATE Table1 SET [id] = [Id] + 1

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 04:00:48

is id your primary key column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-10 : 04:45:09

is id an identity column ?

KH
[spoiler]Time is always against us[/spoiler]

luarit
Starting Member

6 Posts

Posted - 2012-01-10 : 05:52:05

Yes, it was my primary key, I want to merging several database in only one. I give this example but it is not always adding +1 some times Id=2 will be 4,...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 06:50:44

quote:
Originally posted by luarit

Yes, it was my primary key, I want to merging several database in only one. I give this example but it is not always adding +1 some times Id=2 will be 4,...

why do you need to shift records like this? are you trying to fill up gaps? i dont think this is required at all

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources