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

Accepted Answer

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.

SQL Server : increment INT column using variable

Solution

Problem