AutoNumber Integer to Varchar with min ID

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

djredden73
Starting Member

2 Posts

Posted - 2015-02-25 : 12:17:58

Thank you djj55.. That worked!

DAVID J REDDEN

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2015-02-25 : 13:29:01

You are welcome.

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources