Menu
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?
986
The Rank function can be used to generate a sequential number for each row or to give a rank based on specific criteria. The ranking function returns a ranking value for each row. However, based on criteria more than one row can get the same rank.
To number rows in a result set, you have to use an SQL window function called ROW_NUMBER() . This function assigns a sequential integer number to each result row.
Sequences objects are created by using the CREATE SEQUENCE statement. Sequences are integer values and can be of any data type that returns an integer. The data type cannot be changed by using the ALTER SEQUENCE statement. To change the data type, drop and create the sequence object.
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
