Update SQL with consecutive numbering

I want to update a table with consecutive numbering starting with 1. The update has a where clause so only results that meet the clause will be renumbered. Can I accomplish this efficiently without using a temp table?

Solution 1:

This probably depends on your database, but here is a solution for MySQL 5 that involves using a variable:

SET @a:=0;
UPDATE table SET field=@a:=@a+1 WHERE whatever='whatever' ORDER BY field2,field3

You should probably edit your question and indicate which database you're using however.

Edit: I found a solution utilizing T-SQL for SQL Server. It's very similar to the MySQL method:

DECLARE @myVar int
SET @myVar = 0

UPDATE
  myTable
SET
  @myvar = myField = @myVar + 1

Solution 2:

For Microsoft SQL Server 2005/2008. ROW_NUMBER() function was added in 2005.

; with T as (select ROW_NUMBER() over (order by ColumnToOrderBy) as RN
        , ColumnToHoldConsecutiveNumber from TableToUpdate
    where ...)
update T
set ColumnToHoldConsecutiveNumber = RN

EDIT: For SQL Server 2000:

declare @RN int
set @RN = 0 

Update T
set ColumnToHoldConsecutiveNubmer = @RN
    , @RN = @RN + 1
where ...

NOTE: When I tested the increment of @RN appeared to happen prior to setting the the column to @RN, so the above gives numbers starting at 1.

EDIT: I just noticed that is appears you want to create multiple sequential numbers within the table. Depending on the requirements, you may be able to do this in a single pass with SQL Server 2005/2008, by adding partition by to the over clause:

; with T as (select ROW_NUMBER() 
        over (partition by Client, City order by ColumnToOrderBy) as RN
     , ColumnToHoldConsecutiveNumber from TableToUpdate)
update T
set ColumnToHoldConsecutiveNumber = RN

Solution 3:

If you want to create a new PrimaryKey column, use just this:

ALTER TABLE accounts ADD id INT IDENTITY(1,1)