SQL Query to Delete Duplicates

Hello,

I have a table example:

ID | Name | Email

I want to delete duplicate records/rows where email is the duplicate.

1 | F |
2 | D |
3 | P |

I need an sql query that will delete the duplicated records and maintain only one. It doesn't matter which ones get deleted.

Thanks in advance for any help!

 

Check out this thread, I think it does what you need...backup your data first

http://www.sitepoint.com/forums/showthread.php?t=305399&highlight=delete+duplicate+rows

 

Hi Check if this is USEFUL..

http://www.sqlteam.com/item.asp?ItemID=3331
Njoy..

 

I just did this the other day here is how i did it

I had a bunch of zipcodes i wanted to remove the doups. I found this the easiest way to do it in PHP

mysql_connect("$db_host","$db_user","$db_passwd");
mysql_select_db("$db_db");

$results = mysql_query("SELECT zipcodes, COUNT(*) as count FROM zipcodes GROUP BY zipcodes order by count desc");

while($row = mysql_fetch_object($results))
{

$zipcodes= $row->zipcodes;
if ($count >1) {
print "$zipcodes<br> ";
mysql_query("delete from zipcodes where id='$id'");


This will loop through each time you run it and delete 1 douplicate where you have more then 1 .

course dont use exactly what i posted it its just off the top of my head from what i remember

 

Not sure if this helps, but it might be worth going back to the original script and placing a check system so users with the same name and email address can not register.

 

well just add a uniq key to the row in the db... that will solve that