how to delete duplicate rows and keep the origianal rows in mysql

[chand basha]

Hi,


how can i delete the duplicate rows and keep the original row in the mysql date.

rs = "select count(email) as mail from import_address_book group by email having mail > 1
rs.each do |mail|
`delete from import_address_book where email limit #{mail - 1}";
end..


please can any correct it and send me the executing query...

[Rabbit]

Without the table structure, there's not much we can help with.

[dlite922]

once you run this query and get the results into $rs:

SELECT COUNT(email) as count, email from import_address_book GROUP BY email HAVING COUNT(email) > 1;
 

 
while(($row = mysql_fetch_assoc($rs))) { 
 
    mysql_query('DELETE FROM import_address_book WHERE email = '.$row['email'].' ORDER BY id_or_date_column DESC LIMIT '.$row['count']-1;
 
}
 
 

That should do it. I have not tested it though.


Dan

[chand basha]

Hi,


thanks for helping me. i got the solution.

I altered this query it is working fine.

Thanks for u r help

[dlite922]

Can you post how you altered it so that anybody in the same situation can solve it as well?

Thanks,

Dan