Originally posted by Jeanne Boyarsky:
Corey,
The easiest way I can think of is to copy all the records to a temp table and then re-insert them using select distinct.
While I can imagine a way of identifying duplicates (using group by and having), I can't think of a way to delete them in a non database specific way. Of course, you probably don't care about that as you are dealing with a specific database. Which one?
I'm curious if anyone has a reply that doesn't involve a temp table.
ahh but Jeanne... where's the challenge in that? :p
Use a subquery with an aggregate function such as MIN to arbitrarily choose the ID to retain:
delete from mytable
where myPK not in ( select min(myPK)
from mytable
group by SomeID )
[ November 27, 2007: Message edited by: Paul Campbell ]