Renumber records without a cursor? 7

[hirick]

Does anyone have a clever method to renumber a display order field?

Users add records to the end of a list, and delete from within the list. I want to sort and renumber the rows on command.

Obviously I can do it using a cursor, but I'm hoping to find a way to do it in a single UPDATE statement.

Thanks.

 

[MalcolmW]

declare @variable int
set @variable = 0
update table
SET @variable = column = @variable + 1

the SET statement is read Japanese style, right to left.
First the column is assigned the value of the variable plus one, then the variable is assigned the new value of the column. The process repeats in a single pass for the whole table.

Yeah, I hate cursors too.

 

[hirick]

Thanks, Malcolm. This is an elegant solution!

 

[MalcolmW]

Not mine - I should give credit here - from Inside SQL Server 7.0, although they use it as an example for running totals. It made reading all that MS propaganda easier to take.
There are several derivatives of this that I have used to avoid cursors - a nice little feature in SQL Server.

 

[clawton]

Wow, Nice one Malcolm.

Thanks.

 

[christine1]

Malcolm,

I too hate cursors and beleive they are contributing to the lack of performance in my stored procedures (in other words, they run slowly) Do you have any suggestions or resources that will assist me in "fixing up my sproc's?"
Any advice is appreciated.

Thanks

Christine

 

[MalcolmW]

I don't know of any easy answer to that. Because SQL is a set language, where operations are performed on sets of data, cursors are a notable exception.
The most useful "tools" I use to avoid cursors are
-the CASE statement,
-the SET @variable = column = expression,
-the combination of the above (the expression can include a CASE statement - this is powerful),
-increasingly complex SQL, including derived tables and scalar subqueries.

Some cursors are so difficult to avoid it is better to using them, as the time spent writing/maintaining the SQL would be better spent on upgrading hardware to improve performance by brute force.

There, those are my thoughts - any other cursor haters out there that tricks to share?

 

[foxdev]

Thanks, MalcolmW - it took a lot of searching to find this thread, but it definitely saved me a lot of time today - I gave you a star when you first posted it, so I won't repeat - but you have my grattitude. Robert Bradley

http://www.foxdev.com/

Got extra money lying around? Visit:

http://www.agrainofhope.org