[Pugged] Duplicates

[Tim Perdue]

Dave J. Hala Jr. wrote:
> I'm looking for an easier way to check for duplicates in a mysql table.
> 
> Here is my current process:
> 
> 1. create a temporary table
> 2. read a record from the temporary table
> 3. search the original table for more than one match.
> 4. if there is more than one match, mark that record
> 5. show the dba the original record and marked record.
> 6. if dba selects the marked record delete it.
> 7. drop the temporary table
> 
> 
> I seem to vaguely remember something in access that compared keys or
> something. I remember it was a very, very simple thing that worked
> really easily.
> The pain of what I am doing is that I'm only looking for a match on any
> one or more of a number of the fields in each record. I also need to
> display the suspected duplicates so that someone can confirm it is
> duplicate. Once it is confirmed, then it gets purged.
> 
> Anyone know of a better way?


Can you select key1,key2,key3,count(*) GROUP BY key1,key2,key3??

You could make it even more elaborate, and add a HAVING clause to the 
end, so it only selects where count > 1.

Better yet, add a unique index to the table so duplicates cannot be 
inserted no matter what.

Tim