List:General Discussion« Previous MessageNext Message »
From:Jonathan Duncan Date:July 9 2004 11:25pm
Subject:RE: Weeding out duplicates
View as plain text  
For the information of someone who may need it in the future.  I used
Jeffrey's idea for determining duplicates.  Then I created a temporary
table, and used insert...select to put the id's of the duplicates in the
temporary table.  Then it was a simple "delete from table where
temp.id=table.id".

Thanks for the help.

Jonathan Duncan
 
 
>>>"Jonathan Duncan" <jduncan@stripped> 07/09 10:25 am >>> 
Lachlan, 
 
I want to identify the entries in the table where the email addresses 
are the same as another entry.  Whatever else is in the record does not 
matter to me. 
 
However, a second requirement for the query is that it show me the last 
duplicate instead of the first.  This way I keep the first entries and 
remove subsequent ones. 
 
Thanks, 
Jonathan Duncan 
 
 
>>>"Lachlan Mulcahy" <asqui@stripped> 07/08 10:59 pm >>> 
 
Jonathan, 
 
I'm not exactly sure what you want to do.. 
 
Do you want to identify the entries in the table where the email 
addresses 
are the same as another entry but the name and address details differ...

 
or.. do you want to find entries where the name and address information 
is 
the same but email addresses differ? 
 
Lachlan 
 
-----Original Message----- 
From: Jonathan Duncan [mailto:jduncan@stripped] 
Sent: Friday, 9 July 2004 7:54 AM 
To: mysql@stripped 
Subject: Weeding out duplicates 
 
 
I am trying to get rid of duplicate user info entries in a database.  I 
am assuming that the email address is unique but other information, like

 
first name, last name, address, etc are not.  The "email" field is not a

 
unique field in the database.  I was trying something like the 
following, but not getting what I wanted. 
 
select distinct u1.id,u1.firstname,u1.lastname,u1.email from Users u1, 
Users u2 where u1.email=u2.email; 
 
How can I go about identifying the duplicate entries of email addresses?

 
 
Thank you, 
Jonathan Duncan 
 
-- 
MySQL General Mailing List 
For list archives: http://lists.mysql.com/mysql 
To unsubscribe:   
http://lists.mysql.com/mysql?unsub=1 
 
 
 
 
-- 
MySQL General Mailing List 
For list archives: http://lists.mysql.com/mysql 
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

 
 
 
-- 
MySQL General Mailing List 
For list archives: http://lists.mysql.com/mysql 
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

 
Thread
Weeding out duplicatesJonathan Duncan8 Jul
  • RE: Weeding out duplicatesLachlan Mulcahy9 Jul
Re: Weeding out duplicatesjeffrey_n_Dyke9 Jul
Re: Weeding out duplicatesJonathan Duncan9 Jul
RE: Weeding out duplicatesJonathan Duncan9 Jul
  • RE: Weeding out duplicatesRobert A. Rosenberg11 Jul
RE: Weeding out duplicatesJonathan Duncan10 Jul
  • RE: Weeding out duplicatesRobert A. Rosenberg11 Jul
RE: Weeding out duplicatesJonathan Duncan12 Jul
RE: Weeding out duplicatesSGreen14 Jul