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

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 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

Answer 3

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

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

Answer 4

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.

Answer 5

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 6

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.

Answer 7

UPDATE TableName
SET TableName.id = TableName.New_Id
FROM (
  SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS New_Id
  FROM TableName
  ) TableName

Answer 8

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.

Answer 9

One more way to achieve the desired result

1. Create a sequence object - (https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver16)

 CREATE SEQUENCE dbo.mySeq
        AS BIGINT
        START WITH 1 -- up to you from what number you want to start cycling
        INCREMENT BY 1 -- up to you how it will increment
        MINVALUE 1
        CYCLE
        CACHE 100;

2. Update your records

UPDATE TableName
SET Col2 = NEXT VALUE FOR dbo.mySeq
WHERE ....some condition...

EDIT: To reset sequence to start from the 1 for the next time you use it

ALTER SEQUENCE dbo.mySeq RESTART WITH 1 -- or start with any value you need`

Answer 10

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://web.archive.org/web/20150411042510/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 11

Hope this single statement solution will help. Assuming you have a column id in the table. You need to replace table, your-integer-column and your-where-clause-column placeholders.

UPDATE {{table-abc}} a 
SET {{your-integer-column}} = (
  SELECT COUNT(id)+1 
  FROM {{table-abc}} 
  WHERE {{your-where-clause-column}} = {{a.your-where-clause-column}} 
  AND id > a.id 
);

Answer 12

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.