Delete Duplicate Records?

Hi i have a database for all my blog posts. There are around 6500 posts. So there are 6500 values in the database dle_post. But i found that only 5800 blog posts are unique. The others are duplicates. Its because, the site is run by several authors who post new posts everyday. I used this command in PhpMyAdmin and the duplicate posts were identified. but how do i delete the duplicates leaving the originals.

SELECT title, COUNT(*) FROM dle_post
Group BY title HAVING COUNT(*) > 1

 

following will help provided your table has an auto_increment column. (Id in following query)

CREATE TABLE temp_delete_post SELECT Id, title, COUNT(*) FROM dle_post GROUP BY title HAVING COUNT(*) > 1;

DELETE FROM dle_post WHERE Id IN (SELECT Id FROM temp_delete_post);

 

Thank you for the excellent help! But this does not keep even 1 but entirely transfers all the duplicates isn't it?

 

two queries when executed will delete 1 title from the ones which are duplicate. However, if particular title is duplicated more than 2 times then this query will not remove them at once. You might have to write a script which removes them case by case with count or execute above queries several times until affected rows is not ZERO.

 

Ok. i got it. See, if i have unique ids for three values (just example). One of them is repeated 6 times and the other two are repeated 2 times. Does this means that when i run those 2 queries first time, the 2nd and 3rd values' duplicates will be deleted but i have to run the 2 queries 4 more times to completely remove the duplicates. Is that correct?

 

This works like a charm. everyday, my users are submitting around 200 articles and i get many duplicates. Thanks to you, now that my database will be optimized!

 

I'm happy that it could help you.

Try searching for a plugin or may be you can write a script which periodically keeps cleaning the posts automatically

 

I could not even think of writing a script as im a noob in MySql. If you ever come across a plugin, please post it here. However, even doing it manually atleast once in 3 days is enough for me to keep the database optimized. Thanks once again.

 

Any one know about, how to delete duplicate threads in VB forum

 

Google is your friend.

Plugin to delete duplicate posts.
http://wordpress.org/extend/plugins/delete-duplicate-posts/

Always start a new thread if you want to ask something. Asking in an existing thread may distract people from answering the original question.

This vbulletin forum thread may help you.