472,110 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,110 software developers and data experts.

Recursive Update Query for SQL server


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.
Aug 25 '08 #1
2 4801
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:
Expand|Select|Wrap|Line Numbers
  1. sp_rename 'TableName', 'TableName_old';
Step 2:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE [TableName] (Id INT IDENTITY, Name VARCHAR(50));
  2. INSERT INTO TableName (Name)
  3.     SELECT Name from TableName_old;
Aug 31 '08 #2
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.
Sep 1 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by JP SIngh | last post: by
4 posts views Thread by Karaoke Prince | last post: by
14 posts views Thread by Bob | last post: by
3 posts views Thread by Dan Berlin | last post: by
17 posts views Thread by kalamos | last post: by
10 posts views Thread by AsheeG87 | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.