From: Gerald Clark
Date: April 16 1999 1:34pm
Subject: Re: delete duplicate
List-Archive: http://lists.mysql.com/mysql/1951
Message-Id: <37173C7A.41C6@suppliersystems.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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
> **