?
Solved

How to progressivly increment subsequent fields in t-sql

Posted on 2008-02-06
8
Medium Priority
?
1,993 Views
Last Modified: 2008-03-06
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
0
Comment
Question by:simshp
  • 3
  • 3
  • 2
8 Comments
 
LVL 143

Expert Comment

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

Open in new window

0
 
LVL 22

Accepted Solution

by:
dportas earned 1000 total points
ID: 20835286
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
 

Author Comment

by:simshp
ID: 20837066
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 22

Expert Comment

by:dportas
ID: 20837115
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20839072
I agree with dportas.
did you try his suggestion?
0
 

Author Comment

by:simshp
ID: 20839738
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
 
LVL 22

Expert Comment

by:dportas
ID: 20840144
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
 

Author Comment

by:simshp
ID: 20841610
Thanks ... thats great ... really a powerful statement !
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

594 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question