Update column with incrementing number

Today a friend of mine was stunned by an update statement I wrote which updated a column by incrementing values. Actually it’s a very simple update statement construct but according to my friend, not many people know about.
Here is an example statement used to update a column with incrementing number.

DECLARE @num AS INT
SET @num = 1

UPDATE Employee SET @num = counter = @num + 1

If you want to use the incrementing number to perform some operation on the column to be updated then you can use this technique. Below is couple of update statements to get your ideas ticking.

UPDATE Student SET
	Marks = Marks + @num,
	@num = @num + 1;

UPDATE Employee SET 
	EmpCode = 'EM' + CAST(@num AS VARCHAR(8)),
	@num = @num + 1;
Advertisements

About Amit Singh

Software engineer; crazy about music, astronomy and radio controlled aircrafts. Big science/technology buff!
This entry was posted in Programming, SQL and tagged , , , . Bookmark the permalink.

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