SQL Server : increment INT column using variable

Question

Trying to insert an order column into some records, based on another field. Normally not a problem in MySQL, but in SQL Server I can't quite understand the syntax here.

This is what I have:

DECLARE @a int
SET @a = 1

UPDATE tablename 
SET order_position = @a:=@a+1  
WHERE table_id = xxx

But part of me thinks this is going down the route of a function/procedure as opposed to a one hit UPDATE query.

Sorry, but I wrote this as a MySQL database person, not familiar with variables with SQL Server so could be a little wrong.

I need to run this on a load of records one by one, and I want the order_position column to be 1-7 (where there are 7 records), etc..

Thanks, Chris

Answer 1

Try this code:

DECLARE @a int
SET @a = 1
UPDATE tablename SET order_position = @a, @a=@a+1  WHERE table_id = xxx

You are trying to do a double assignment which is the issue. "order_position = @a:=@a+1" has 2 interpretations, one you know and the other is that the result of incrementing a, that it succeeded is what should go in the order_position value.

Answer 2

Separate the variable incrementing from the field update.

DECLARE @a int
SET @a = 1
UPDATE tablename 
SET order_position = @a
   ,@a = @a + 1  
WHERE table_id = xxx

Coming from MySQL you may be overlooking a great tool for this task, ROW_NUMBER().

You can use ROW_NUMBER() to assign a number to each row in a table:

SELECT *,ROW_NUMBER() OVER (PARTITION BY ....  ORDER BY .... )
FROM Table

PARTITION BY indicates a grouping for numbering, ie there will be a '1' for each combination of fields used in the PARTITION BY, and they will of course be ordered from 1-n based on the ORDER BY clause.