List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:April 16 1999 1:34pm
Subject:Re: delete duplicate
View as plain text  
Jeff Hill wrote:
> 
> On the same subject, how would you identify all records with a
> duplicate/matching field?
> 
> I have users who register for access to my website, then forget
> and register again. The only field that is definitely duplicated
> is e-mail. I can't just delete the duplicate records; I need to
> only list them and then contact the users.
> 
> Thanks,
> 
How about:
select email, count(*) as cnt from tablename group by email having cnt>1

This will return a list of emails with duplicate records, and their
counts.

Use this list is input for another program that sends notices to users.


> Jeff Hill
> 
> Gerald Clark wrote:
> >
> > Xah Lee wrote:
> > >
> > > Suppose I have a table that has identical rows for some reason. I need to
> > > delete the duplicates. Can this be done in MySql's SQL?
> > >
> > Here is one method that uses a temporary unique index to force deletion
> > of duplicate records.
> >
> > alter ignore table tablename add unique( field1, field2, ... fieldx )
> >
> > When you are  through, drop the index MySQL created.
> >
> > ---------------------------------------------------------------------
> 
> *********   HR On-Line:  The Network for Workplace Issues
> ********
> ** Ph:416-604-7251 -- Fax:416-604-4708 ** http://www.hronline.com
> **

Thread
delete duplicateXah Lee13 Apr
Re: delete duplicateDon Read13 Apr
Re: delete duplicateGerald Clark13 Apr
Re: delete duplicateJeff Hill13 Apr
Re: delete duplicateGerald Clark16 Apr