How to progressivly increment subsequent fields in t-sql

please try this:

DECALRE @ID INT
SET @ID = 0
UPDATE yourtable
  SET @ID = Vieworder = @ID + 1

Select allOpen in new window

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.

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

I agree with dportas.
did you try his suggestion?

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

Select allOpen in new window

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;

Thanks ... thats great ... really a powerful statement !