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;