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.