CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Peter Sun
Peter Sun

Reputation: 1813

Increment column value by one

I am trying to update a column (int) and it is not incrementing by 1 like I want it to, but it's incrementing by every 8 rows. So it is doing this:

Table1:

ID  FIELD_NUMBER
----------------
1    3507572
2    3507572
3    3507572
4    3507572
5    3507572
6    3507572
7    3507572
8    3507572
9    3507573
10   3507573
11   3507573
12   3507573
13   3507573
14   3507573
15   3507573
16   3507573

It should increment by 1 3507572, 3507573, etc

Code:

DECLARE @id INT 
SET @id = 3507571 

UPDATE table1
SET @id = FIELD_NUMBER = @id + 1 
GO

Not sure why. I am using SQL Server 2012. Thoughts?

Upvotes: 3

Views: 3692

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

A better approach is to use an updatable CTE:

DECLARE @id INT;
SET @id = 3507571;

with toupdate as (
      select t1.*, row_number() over (order by field_number) as seqnum
      from table1 t1
     )
update toupdate
    set field_number = @id + seqnum;

Upvotes: 3

Related Questions