[Resolved] deleting duplicated content from mysql, how?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

[Resolved] deleting duplicated content from mysql, how?

Post 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?
Last edited by kaisellgren on Mon Mar 19, 2007 12:33 pm, edited 1 time in total.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Post 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.
Post Reply