Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to renumber records, do running totals without a cursor

T-SQL Hints and Tips

How to renumber records, do running totals without a cursor

by  MalcolmW  Posted    (Edited  )
This trick is from Inside SQL Server 7.0, and is a fast and neat way to renumber records (see example). It can also be used for running totals, or a variety of things that would normally require a cursor.

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.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top