Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 AutoNumber Integer to Varchar with min ID

Author  Topic 

djredden73
Starting Member

2 Posts

Posted - 2015-02-25 : 10:20:18
I have a table with fields: FirstName Last_Name Date_of_Birth and ID that could have many "flavors" to First_Name & Last_Name.
Currently the table has an ID which is an AutoNumber, Integer.

Example:

FIRST_NAME MIDDLE_INITIAL LAST_NAME DOB MEMBER_ID ID
John Doe 01/01/1984 AB123456 1
John J Doe 01/01/1984 AB123456 2
John James Doe 01/01/1984 AB123456 3
James Smith 12/01/1965 ZY987654 4
James K Smith 12/01/1965 ZY987654 5
James Smith 08/20/1973 BB754321 6

I am looking/hoping to accomplish the following.

Keep all rows; but have the ID update to the minimum ID.


FIRST_NAME MIDDLE_INITIAL LAST_NAME DOB MEMBER_ID ID
John Doe 01/01/1984 AB123456 1
John J Doe 01/01/1984 AB123456 1
John James Doe 01/01/1984 AB123456 1
James Smith 12/01/1965 ZY987654 4
James K Smith 12/01/1965 ZY987654 4
James Smith 08/20/1973 BB754321 6

My SQL skills drop off to figure out how to do this.
Any help would be greatly appreciated.

Modified original post. This example is more realistic with a Member_ID as the unique key.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2015-02-25 : 12:14:50
Try using a CTE
WITH cte AS (select Member_ID, MIN(ID) AS MinID FROM T1 GROUP BY Member_ID) 
UPDATE T2
SET ID = T1.MidID
FROM T2
INNER JOIN CTE ON T2.Member_ID = CTE.Member_ID


djj
Go to Top of Page

djredden73
Starting Member

2 Posts

Posted - 2015-02-25 : 12:17:58
Thank you djj55.. That worked!

DAVID J REDDEN
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2015-02-25 : 13:29:01
You are welcome.


djj
Go to Top of Page
   

- Advertisement -