How to progressivly increment subsequent fields in t-sql

Hi there
I have a table : page
ID   ViewOrder
1      0
2      0
3      0

I want to increment the view order for each record by 1. So the table should look like

1   1
2   2
3  3

With an update statement in sql 2005.
Thanks
simshpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:
DECALRE @ID INT
SET @ID = 0
UPDATE yourtable
  SET @ID = Vieworder = @ID + 1

Open in new window

0
dportasCommented:
Assuming you want the vieworder to match the order of id you can do this:

WITH T AS
 (SELECT id, vieworder,
  ROW_NUMBER() OVER (ORDER BY id) rn
  FROM tbl)
UPDATE T SET vieworder = rn;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
simshpAuthor Commented:
Actually the ID is unrelated to the vieworder column.

the table could be :
ID    ViewOrder
23    1
34    2
66    3

Just want to able to update the vieworder column by one more than the previous record, which would be the previous ID inorder. So a higher ID will always have a vieworder number (+1) greater  then the previous ID.
Will give the above solutions a go tomorrow and let you know.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dportasCommented:
Bear in mind that there is no such thing as an ordered update. Angel's solution is fast but the ordering is not guaranteed and may be somewhat unpredictable. The advantage of using ROW_NUMBER is that you can specify the ORDER BY clause - the update isn't "ordered" but the value returned by the ROW_NUMBER function is. For it to be completely predictable the ID values must be unique.

HTH
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with dportas.
did you try his suggestion?
0
simshpAuthor Commented:
Hi there
Thanks dportas, it seems to have done the trick. I used your code in the query attached. Out of interest do you think this could have been done without a cursor ?


Cheers


	DECLARE @NodeId int
	
	DECLARE Node CURSOR FOR
		SELECT DISTINCT NodeId
		FROM tbl_node
		ORDER BY NodeId
 
	OPEN Node; FETCH Node INTO @NodeId
 
	WHILE @@FETCH_STATUS = 0 BEGIN
		WITH T AS
		(
			SELECT pageid, vieworder,
			ROW_NUMBER() OVER (ORDER BY pageid) rn
			FROM tbl_page
			WHERE NodeId = @NodeId
		)
		UPDATE T SET vieworder = rn;
	FETCH Node INTO @NodeId
END
CLOSE Node; DEALLOCATE Node

Open in new window

0
dportasCommented:
You definitely don't need a cursor. Just use the PARTITION clause:

WITH T AS
 (
  SELECT pageid, vieworder,
  ROW_NUMBER() OVER (PARTITION BY NodeId ORDER BY pageid) rn
  FROM tbl_page
 )
 UPDATE T SET vieworder = rn;
0
simshpAuthor Commented:
Thanks ... thats great ... really a powerful statement !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.