Delete Duplicate Records Problem

[jpicks]

I am having difficulty trying to delete duplicate records in a table. The table has two columns "Social Security Number" and "Date of Birth". The problem is that there is no unique identifier on the table. The records are all unique when you combine ssn and dob, but there are multiple ssn with different dob.

I need to clean the data up so that there are no duplicate ssn in the table. The duplicate ssn all have different dob and we don't care what dob gets paired with the ssn.

Any suggestions?

I am completely out of ideas and the boss needs it done by 9:00 tomorrow.

Any help or suggestions would be greatly appreciated

 

[supersugar]

Hi,
You can select distinct record to a temporary table and delete the whole records. After that, insert back to the original table. You can use the DTS to do this task if you want it be done every month or week.(Chong)

 

[tlbroadbent]

Here is a complete script that should work for you with slight modification.

--Create a temp table with distinct
--rows from the original table
Select distinct *
Into #temp
From YourTable As t
Where dob=
--select max dob. you may want to use min?
(Select max(dob)
From YourTable Where ssn=t.ssn)

--Add identity to the temp table
Exec("Alter table #temp add RecID int identity&quot

--Delete rows with duplicate ssn and dob
Delete
From #temp
Where RecID >
(Select Min(RecID)
From #temp t
Where t.ssn=#temp.ssn)

--Remove rows from original table
Truncate table YourTable
--Use delete if you are not an SA or DBO
--Delete YourTable

--Insert unique rows into original table
Insert Yourtable
Select * From #temp

--Add a unique index or primary key to
--prevent duplicates in the future

--Cleanup
drop table #temp Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Transcend]

Hi there,

are you trying to delete them through enterprise manager? If so SQL has a problem when there is no key and there are duplicate records. Try running a delete query in query analyser and this should solve your problem.

Thanks and good luck!
Transcend

 

[jpicks]

Thanks to all for your replies!

Terry, your script worked great. I only needed to use part of it to copy the clean data to a new table. We needed to clean up the data before importing it into a new system we are implementing this morning.

Here is the part of Terry's script that I used to copy only unique data over to a temp table:

Select distinct *
Into #temp
From YourTable As t
Where dob=
--select max dob. you may want to use min?
(Select max(dob)
From YourTable Where ssn=t.ssn)

Again. Thanks to all. You made my day!