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?
11 Answers
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 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
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.
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
UPDATE TableName
SET TableName.id = TableName.New_Id
FROM (
SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS New_Id
FROM TableName
) TableName
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.
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).
One more way to achieve the desired result
1. Create a sequence object - (https://docs.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...
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.
Related