Page 1 of 1

[Resolved] deleting duplicated content from mysql, how?

Posted: Mon Mar 19, 2007 12:09 pm
by kaisellgren
Hi,

I have a mysql table which has duplicated content. I want to delete all duplicated content, except leave one row. any suggestions?

Posted: Mon Mar 19, 2007 12:17 pm
by Kieran Huggins
hmm.. maybe you could create a temp table and fill it with "SELECT *, DISTINCT {rowname} as uniqueRow....GROUP BY {rowname}"?

or you could pull it all into PHP and do the comparison there, writing the results back to the DB...

Posted: Mon Mar 19, 2007 12:22 pm
by feyd
Write a query that orders all the duplicates together. Using an array to store the id's of records, iterate over the result set storing the id of each record which matches the previous record. When the newest record changes remove one of the id's (to keep) and delete the rest. After which you reset the array and start storing id's again. Wash, rinse, repeat.

Posted: Mon Mar 19, 2007 12:33 pm
by kaisellgren
Thanks for help.

I think I'll use DISTINCT way, because I just figured it out myself too. Pretty simple.

Thanks and see you. Topic resolved.