Add one to each row in a specific column

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

Add one to each row in a specific column

Author

Topic

SidewaysFish
Starting Member

6 Posts

Posted - 2007-09-22 : 13:48:15

I need to increment a value in some rows to keep a list in order. Here's the idea:

UPDATE myList SET OrderNum = OrderNum + 1 WHERE UserID = @UserID, OrderNum >= @OrderNum

This throws an exception. Is there a quick and easy way to update like this?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-22 : 14:50:40

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 1

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SidewaysFish
Starting Member

6 Posts

Posted - 2007-09-22 : 15:11:35

Thanks, that's close but not quite what I'm trying to do.
I have a list that is already populated. I want to change some of the values in it.

Input
OrderNum
1
2
3
4

then I run:
UPDATE myList SET OrderNum = OrderNum + 1 WHERE OrderNum >= @OrderNum
with @OrderNum = 2

Output
OrderNum
1
3
4
5

So each value of OrderNum above @OrderNum is incremented.
I also need to write another that ends with
WHERE OrderNum >= @StartOrderNum, OrderNum <= @EndOrderNum

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-24 : 01:47:33

Try

UPDATE myList SET OrderNum = OrderNum + 1
OrderNum >= @StartOrderNum and OrderNum <= @EndOrderNum

Madhivanan

Failing to plan is Planning to fail

SidewaysFish
Starting Member

6 Posts

Posted - 2007-09-24 : 12:31:46

Wow, thanks madhivanan. You're response was so simple I thought you were making fun of me, but that works.

To recap, if you want to add one to a specific column in a bunch of rows, use:
UPDATE myList SET OrderNum = OrderNum + 1 WHERE OrderNum >= @StartOrderNum AND OrderNum <= @EndOrderNum

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-25 : 01:55:23

quote:
Originally posted by SidewaysFish

Wow, thanks madhivanan. You're response was so simple I thought you were making fun of me, but that works.

To recap, if you want to add one to a specific column in a bunch of rows, use:
UPDATE myList SET OrderNum = OrderNum + 1 WHERE OrderNum >= @StartOrderNum AND OrderNum <= @EndOrderNum

Well. There is no need to have unneccesary fun

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources