Increment a value in the SET clause of an UPDATE Statement in SQL

I was working on a task in which I have to add one new column in my existing table and set its value given by the client which is incremental value to previous records.

For e.g.

Id         fname              lname               No

1          user 1              user 1              5

2          user 1              user 1              6

3          user 1              user 1              7

I was looking for some solution and ended up with following solution which works well for me.

-- Create Table
CREATE TABLE usermaster (
fname VARCHAR(20)
,lname VARCHAR(20)
)
-- Insert 100 Records
INSERT usermaster
VALUES (
'user fname'
,'user lname'
) GO 100 -- You can specify any no. of records
-- Add one more column called id using ALTER statement
ALTER TABLE usermaster ADD id INT
-- Update Id column with Increment value
DECLARE @id INT
SET @id = 0 -- you can specify any no. I set it 0 so it start with 1.
UPDATE usermaster
SET @id = id = @id + 1
GO
-- check updated table records
SELECT *
FROM usermaster
GO

Happy Querying 🙂

Advertisements

One thought on “Increment a value in the SET clause of an UPDATE Statement in SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s