CODEWITHSUNDEEP
sqlsql-serverloopsauto-incrementrow-number
Jerry
Jerry

Reputation: 51

SQL - how to update a column with each row value incremented by 1?

For the selected rows in a column, how to update each row sequentially from the beginning to the end, with each row value incremented by 1 (or certain number). I know this can be done in excel in few seconds but I could figure out how to achieve in SQL server. For instance:

customer id is NULL now update customer id with every row incremented by 1 (i.e. first row = 1, second row = 2, .....nth row = n)

ship-to party   customer id 
0002018092      NULL
0002008127      NULL
0002000129      NULL
0002031592      NULL
0002034232      NULL

desired output 

ship-to party   customer id
0002018092      1
0002008127      2
0002000129      3
0002031592      4
0002034232      5

Also, for the selected rows in a column, how to update each row with the row number? I know there is a row_number() function but didn't succeed in producing the desired result. for instance

column A is NULL now update Column A with every row incremented by 1 (i.e. first row = row number 1, second row = row number 2, .....nth row = row number n)

Any demonstration would be very helpful.thkans

Upvotes: 2

Views: 39892

Answers (6)

The BigGunn
The BigGunn

Reputation: 36

This is a very old question, but I have a similar need; to create an incrementing value for each row, however I cannot add a column and update it and none of these solve the problem.

The data I have: id, image file name, created date. I need to create a URL with an incrementing reference that is not held in the db but is a number sequence.

I declare the starting value, then add rownumber

DECLARE @seq INT = 2425;--one less than needed
SELECT 
[Order]=@seq+ROW_NUMBER() OVER (ORDER BY [createDate] ASC)
,[URL]='<img style="width: 600px; height: 400px;" src="/media/'+(CAST (@seq+ROW_NUMBER() OVER (ORDER BY createDate ASC) AS NVARCHAR))+'/'+[text]+'?width=600&amp;height=400" alt="" rel="'+CAST([id] AS NVARCHAR)+'" data-id="'+CAST([id] AS NVARCHAR)+'" />' 
FROM [data] 
WHERE [createDate]>'2024-09-28 00:29:00'

script results

Upvotes: 0

manojt2501
manojt2501

Reputation: 11

Don't think very complex. Try the simple method given below

alter table table_name drop column customer_id
go
alter table table_name add id customer_id IDENTITY(1,1)
go

Upvotes: 1

GuidoG
GuidoG

Reputation: 12014

example : suppose I want to add a value to each value in column SomeIntField in table tblUser

there are 2 ways of doing this easy

first: this just adds value 1 to each column SomeIntField

update tblUser set SomeIntField = SomeIntField + 1

second : this adds an incrementing value, the first row gets +1, second gets +2, and so on...

declare @number int = 0

update tblUser
set    @number = @number + 1,    
       SomeIntField = isnull(SomeIntField, 0) + @Number

EDIT: based on your last comment this might be what you want 

declare @table table (shiptoparty varchar(50), customer_id int)

insert into @Table (shiptoparty, customer_id)
values ('0002018092', NULL), ('0002008127', NULL), ('0002000129', NULL), ('0002031592', NULL), ('0002034232', NULL)

declare @number int = 0

update @table
set    @number = @number + 1,    
       customer_id = isnull(customer_id, 0) + @Number

select * from @table

The result of this is :

shiptoparty | customer_id   
----------- | -----------   
0002018092  | 1 
0002008127  | 2 
0002000129  | 3 
0002031592  | 4 
0002034232  | 5

Upvotes: 3

Amey
Amey

Reputation: 11

To update each row with row number

Try below 

CREATE TABLE tmp(Id INT IDENTITY(1,1), Value INT)

INSERT INTO tmp(value) VALUES(1),(2),(3),(4),(5)

UPDATE T
SET
T.Value = B.RowNo
FROM tmp AS T 
INNER JOIN (SELECT Id, ROW_NUMBER()OVER(ORDER BY Id) AS RowNo FROM tmp)AS B
ON T.Id = B.Id

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

First problem:

you want to increase values in every row in certain column by 1 (or other nuber), try this:

update TABLE_NAME set column_to_increase = column_to_increase + 1

Second problem:

you want to get row number for only certain rows. Solution: first create column holding all row numbers, then get the rows:

select * from (
    select column1, column2, ..., columnN, row_number() over (order by (select null)) as [rn] from MY_TABLE
) where *condition*

FYI: select null in over clause does exactly nothing, it's just there, because window functions (such as row_number) have to have over clause and some of them require order by.

Upvotes: 1

Thom A
Thom A

Reputation: 95554

Rather than using a self referencing variable, use a CTE:

WITH CTE AS (
    SELECT [Your Incrementing Column],
           ROW_NUMBER() OVER (ORDER BY [Columns to Order By]) AS RN
    FROM YourTable)
UPDATE CTE
SET [Your Incrementing Column] = RN;

Edit: To prove a point that ALL rows will be updated:

CREATE TABLE #Sample (String varchar(50),
                      IncrementingInt int);

INSERT INTO #Sample (String)
VALUES ('sdkfjasdf'),
       ('dfydsfdfg'), 
       ('sdfgsdfg45yfg'),
       ('dfgf54d'),
       ('dsft43tdc'),
       ('f6gytrntrfu7m45'),
       ('5d6f45wgby54'),
       ('g34h636j'),
       ('jw'),
       ('h6nw54m'),
       ('g54j747jm5e5f4w5gsft'),
       ('ns67mw54mk8o7hr'),
       ('h45j4w5h4');

SELECT *
FROM #Sample;

WITH CTE AS(
    SELECT IncrementingInt,
           ROW_NUMBER() OVER (ORDER BY String) AS RN
    FROM #Sample)
UPDATE CTE
SET IncrementingInt = RN;

SELECT *
FROM #Sample;

DROP TABLE #Sample;
GO

Upvotes: 1

Related Questions