SQL QUESTION - Delete all duplicate records?

[Superwormy]

Is there any straight SQL I can use to delete all DUPLICATE records in a table?

 

[CTho9305]

something like this might do it:

delete t_tablename t where t.id in (select count(id) from t_tablename where id=t.id and count(id) > 1)

edit: hrmm... I dont like the last count(id) in the subselect... I don't have access to a real SQL server right now so I can't test it and figure out if that really should go there. Test it with a select before doing the delete

 

[Descartes]

You didn't tell us what constitutes a duplicate in your data. I usually do something like the following:

To first identify the duplicates, how many, etc.:

select firstname, lastname, count(*)
from sometable
group by firstname, lastname
having count(*) > 1

That would give me duplicates by firstname and lastname, for example. Then, based on what I find in the above, I might insert into another temp table to see if I can salvage any of the duplicates, or I might simply delete all of them in a manner similar to what CTho has suggested.