Click here to Skip to main content
14,428,429 members
Rate this:
Please Sign up or sign in to vote.
See more:
HI I have a problem with one of my tables.
I originally update it using curtain criteria from another table.

these records then created duplicate entries due to these criteria. I cant link it back up and change the criteria, so I need and alternative to this.

basically when i select the records, they return ass follows :
ID  | Name | Amount | 
1     Jake    2100
1     Kim     450
1     Jake    2100
1     Henry   410

so i want to run one script that will update the id of any duplicate records to return the following :
ID  |  Name | Amount
1     Jake    2100
2     Kim     450
3     Jake    2100
4     Henry   410

any advice?

take into consideration that ill have to first select the "1's" and then update accordingly
Posted
Updated 7-Jul-13 22:24pm
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 2

Hi,

Try this



ALTER TABLE TableName
ADD ID2 INT IDENTITY
   
update TableName set Id=id2  
 
ALTER TABLE TableName 
DROP COLUMN ID2



it hope this will help you
   
Comments
Member 9374423 8-Jul-13 4:07am
   
hmm... this will only set the new column to the same values instead of incrimenting the id's
Adarsh chauhan 8-Jul-13 4:26am
   
I found this the shortest and easiest way to accomplish your motive..
Although there may be some other ways too but that's how I would be doing this..
Member 9374423 8-Jul-13 4:58am
   
the script will definitely consist of an update with a counter
Adarsh chauhan 8-Jul-13 5:23am
   
If you got any other way then please share, it will help me if someday I have to perform same task in different way...
Member 9374423 8-Jul-13 5:31am
   
i will as soon as i find the answer.
Adarsh chauhan 8-Jul-13 5:45am
   
thanks.. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100