Please disable your adblock and script blockers to view this page
 

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. 

Update existing column with linear serial number without loop in sql server


 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. 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon