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