Tutorial :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?


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  


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


In oracle this works:

update myTable set rowColum = rownum  where something = something else  



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

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


I've used this technique for years to populate ordinals and sequentially numbered columns. However I recently discovered an issue with it when running on SQL Server 2012. It would appear that internally the query engine is applying the update using multiple threads and the predicate portion of the UPDATE is not being handled in a thread-safe manner. To make it work again I had to reconfigure SQL Server's max degree of parallelism down to 1 core.

EXEC sp_configure 'show advanced options', 1;  GO  RECONFIGURE WITH OVERRIDE;  GO  EXEC sp_configure 'max degree of parallelism', 1;  GO  RECONFIGURE WITH OVERRIDE;  GO    DECLARE  @id int  SET @id = -1  UPDATE dbo.mytable  SET @id = Ordinal = @id + 1  

Without this you'll find that most sequential numbers are duplicated throughout the table.


To get the example by Shannon fully working I had to edit his answer:

; WITH CTE AS (      SELECT ROW_NUMBER() OVER (ORDER BY [NameOfField]) as RowNumber, t1.ID      FROM [ActualTableName] t1  )  UPDATE [ActualTableName]      SET Name = 'Depersonalised Name ' + CONVERT(varchar(255), RowNumber)  FROM CTE      WHERE CTE.Id = [ActualTableName].ID  

as his answer was trying to update T, which in his case was the name of the Common Table Expression, and it throws an error.


As well as using a CTE or a WITH, it is also possible to use an update with a self-join to the same table:

UPDATE a  SET a.columnToBeSet = b.sequence  FROM tableXxx a  INNER JOIN  (     SELECT ROW_NUMBER() OVER ( ORDER BY columnX ) AS sequence, columnY, columnZ     FROM tableXxx     WHERE columnY = @groupId AND columnY = @lang2  ) b ON b.columnY = a.columnY AND b.columnZ = a.columnZ  

The derived table, alias b, is used to generated the sequence via the ROW_NUMBER() function together with some other columns which form a virtual primary key. Typically, each row will require a unique sequence value.

The WHERE clause is optional and limits the update to those rows that satisfy the specified conditions.

The derived table is then joined to the same table, alias a, joining on the virtual primary key columns with the column to be updated set to the generated sequence.


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



(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).


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.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »