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

Well I need to order the results before I update them, so they are in the right order. We'll call it 'fieldsort' for the purpose of this exercise. — Chris
– Chris, Commented Aug 1, 2013 at 16:27

JB King · Accepted Answer · 2013-08-01 16:21:13Z

22

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.

answered Aug 1, 2013 at 16:21

JB King

11.9k4 gold badges41 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chris Over a year ago

Yes, although after doing this I realised that the order of the rows updated may not be in the right order anyway, so I'm going to need to do a function of some sort. But good to know.

HaBo Over a year ago

I have an extended question, I have a table with millions of records with "ArticleId, ViewCount". Every time a user view a article I update viewCount for that article. There could be 200 requests for view article at a time, that means 200 update queries on the Count table. Will there be any performance issues I should cover for? is there any other best way to accomplish this?

JB King Over a year ago

Multiple updates could lead to deadlocks and other race conditions that could be a problem. Snapshots can be a workaround for slow reads but would create other issues still.

Mhmd Over a year ago

do you know why did not work after update database to 2017(140) ?

Hart CO · Accepted Answer · 2013-08-01 16:44:49Z

6

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.

edited Aug 1, 2013 at 16:44

answered Aug 1, 2013 at 16:21

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

Collectives™ on Stack Overflow

SQL Server : increment INT column using variable

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related