SQL SERVER: Update existing column values with linear serial numbers without loop

Introduction: In this article I am going to share how to update an existing column values with new linear sequence numbers with a single statement without looping through each row of table. Or we can also say resetting the values to new linear sequential values. 

In previous articles i explained How to Auto generate auto incremented unique alphanumeric id or number in sql server and  Temporary tables, their types and examples of use and Stored procedures for insert, update, delete, bind and search operation in table and Get first,last date and total number of days in month and Find all primary and foreign key constraints on each or any table in database

 Implementation: Let’s create a sample table and perform the desired operation.

--Create a table

GO

CREATE TABLE tbLocations

(             

 Location   VARCHAR(100),             

 Priority   INT

)

--Add some dummy data into the table

GO

INSERT INTO tbLocations VALUES

('Mumbai',2),

('Noida',6),

('Kolkata',5),

('Delhi',1),

('Chennai',4),

('Chandigarh',3)

--Check inserted data

SELECT * FROM tbLocations

--Update Priority columns with linear serial number

GO

DECLARE @SrNo INT =0;

UPDATE tbLocations SET @SrNo = Priority = @SrNo + 1

--Now Check inserted data. Priority column is reset to linear sequence 

SELECT * FROM tbLocations

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 

• Tweet
• Share
• Share
• Share

Related Post