deleting duplicate records - help please!! 1

[AndrewWolford]

This seems like it should be so easy but I can't get it to work! I have a table that contains employee SSN's, Names, Salary, etc... Its about 100,000 records long, the problem is that about 40,000 of those records are dupicates. I'm trying to figure out a way to look through the table and if the SSN occurs more than once, delete 1 copy, leaving the other. ONly I can't get it to work right!! Can anyone think of how I can do this? I've been working on this most the day and am in need of a fresh brain... thanks!

 

[cmmrfrds]

One way if table has a unique id. This will save the lowest id value and remove others.

Delete from yourtable yt
where t.id >
(Select min(yt2.id)
from yourtable yt2
where yt.ssn = yt2.ssn)

 

[cmmrfrds]

Should have been yt.id

Delete from yourtable yt
where yt.id >
(Select min(yt2.id)
from yourtable yt2
where yt.ssn = yt2.ssn)

 

[AndrewWolford]

thanks for the help! I'll run the query tonight when I leave work (its going to take forever to run the query - 50,000 x 50,000 calcs to do) and hopefully it will work! Agains, thanks