Update SQL with consecutive numbering

Question

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?

Answer 1

It is possible, but only via some very complicated queries - basically you need a subquery that counts the number of records selected so far, and uses that as the sequence ID. I wrote something similar at one point - it worked, but it was a lot of pain.

To be honest, you'd be better off with a temporary table with an autoincrement field.

Answer 2

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

Answer 3

In oracle this works:

update myTable set rowColum = rownum
where something = something else

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns009.htm#i1006297

Answer 4

Join to a Numbers table? It involves an extra table, but it wouldn't be temporary -- you'd keep the numbers table around as a utility.

See http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

or

http://www.sqlservercentral.com/articles/Advanced+Querying/2547/

(the latter requires a free registration, but I find it to be a very good source of tips & techniques for MS SQL Server, and a lot is applicable to any SQL implementation).

Answer 5

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
    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)
update T
set ColumnToHoldConsecutiveNumber = RN