Increment column value by one

Question

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?

This is not a guaranteed approach. Probably you are getting a parallel plan with DOP of 8. — Martin Smith, Jan 07 '17 at 20:31

Gordon Linoff · Accepted Answer · 2017-01-07T20:38:52.397

3

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;

edited Jan 07 '17 at 20:38

answered Jan 07 '17 at 20:34

Gordon Linoff

1,242,037
58
646
786

Should declare '@id' like normal before the script? – Peter Sun Jan 07 '17 at 20:36
It should be `order by id` I imagine. – Martin Smith Jan 07 '17 at 20:45

Increment column value by one

1 Answers1