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.

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.

	Marks = Marks + @num,
	@num = @num + 1;

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

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s