Update SQL with consecutive numbering

by

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

More Related Contents:

  1. Turning a Comma Separated string into individual rows
  2. Update a table using JOIN in SQL Server?
  3. Calculate running total / running balance
  4. When should I use semicolons in SQL Server?
  5. How to get Time from DateTime format in SQL?
  6. Parse JSON in TSQL
  7. Get size of all tables in database
  8. Fuzzy matching using T-SQL
  9. List of all index & index columns in SQL Server DB
  10. Why use a READ UNCOMMITTED isolation level?
  11. Replace duplicate spaces with a single space in T-SQL
  12. How much size “Null” value takes in SQL Server
  13. SQL Server: Isolation level leaks across pooled connections
  14. How do I use the CONCAT function in SQL Server 2008 R2?
  15. How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?
  16. How do I exclude Weekend days in a SQL Server query?
  17. EXEC sp_executesql with multiple parameters
  18. Preserving ORDER BY in SELECT INTO
  19. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  20. Can you call a webservice from TSQL code?
  21. How to add custom attributes to SQL connection string?
  22. Multiple Column Pivot in T-SQL
  23. Joining multiple tables returns NULL value
  24. Which join syntax is better?
  25. Compute MD5 hash of a UTF8 string
  26. LPAD in SQL Server 2008
  27. How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
  28. How do I remove non-breaking spaces from a column in SQL server?
  29. How to wait for 2 seconds?
  30. What are row, page and table locks? And when they are acquired?

Leave a Comment