Recursive Update Query for SQL server

[omkarkapashikar]

Hi,

I have a table with id and name as column like below.
Id Name
0 a
0 b
0 C

table can have n rows. All n rows contain 0 as id. Now I want to update table such as id column increment like 1,2,3 ,4 for every row.
I want to do that in single update statement without using cursor or any loop.
How can I do that?

Please give me amswer.

[DonBytes]

As far as I know you can't turn on auto increment on a table that looks like that you'd have to do something like:

Step 1:

sp_rename 'TableName', 'TableName_old';

Step 2:

CREATE TABLE [TableName] (Id INT IDENTITY, Name VARCHAR(50));
INSERT INTO TableName (Name)
	SELECT Name from TableName_old;

[omkarkapashikar]

Thanks for your reply,
But I want to do it in a single update statement. Not by using identity column. Hint I got is we have to use "Recursion" . But I am not getting how to use recursion concept over here.