SQL Query to Delete Duplicates

[Fahd]

Hello,

I have a table example:

ID | Name | Email

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

1 | F | email@example.com
2 | D | email@example.com
3 | P | email@example.com

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!

[stumpyPete]

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

[toughguy]

Hi Check if this is USEFUL..

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

[Shoemoney]

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

[l234244]

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.

[Shoemoney]

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