3

I have a lot of duplicate posts. So, how to delete them (only keep 1 post). If they have same title, they are duplicate posts. Thank you very much ! Have a nice day !

  • This is more of a general SQL question. What's your criteria for considering a post a duplicate? – Bendoh Jun 10 '13 at 15:55
  • If they have same title, they are duplicate posts. Thank you for commenting ! – Nguyen Jun 10 '13 at 16:19
4

I'm not entirely sure you can do this with a single query in MySQL as you can't delete from tables which you reference in a sub-query. I would actually recommend doing this using wp-cli and using the WordPress API to delete the duplicate posts (which will also delete any post meta and associated term references):

global $wpdb;

$duplicate_titles = $wpdb->get_col("SELECT post_title FROM {$wpdb->posts} GROUP BY post_title HAVING COUNT(*) > 1");

foreach( $duplicate_titles as $title ) {
   $post_ids = $wpdb->get_col( $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_title=%s", $title ) ); 
   // Iterate over the second ID with this post title till the last
   foreach( array_slice( $post_ids, 1 ) as $post_id ) {
       wp_delete_post( $post_id, true ); // Force delete this post
   }
}

Hope this helps.

1

I know, it's an old thread but since i had a similar problem i would like to add some advises to the Answer from Bendoh (unfortunately i dont have the reputation to make a comment)

Since wordpress and some Plugins are also storing data in the post table, i would recommend to add an post_type to your querry:

global $wpdb;

$duplicate_titles = $wpdb->get_col("SELECT post_title FROM {$wpdb->posts} WHERE `post_type` = 'post' GROUP BY post_title HAVING COUNT(*) > 1");

foreach( $duplicate_titles as $title ) {
    $post_ids = $wpdb->get_col( $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_title=%s", $title ) ); 
    // Iterate over the second ID with this post title till the last
    foreach( array_slice( $post_ids, 1 ) as $post_id ) {
        wp_delete_post( $post_id, true ); // Force delete this post
    }
}

Depending of the source of your duplicates i would also add another field to check for duplicates, like the publication date.

global $wpdb;

$duplicate_titles = $wpdb->get_results("SELECT post_title, post_date, CONCAT(post_date, post_title) AS WHOLENAME FROM {$wpdb->posts} WHERE post_type = 'post' GROUP BY WHOLENAME HAVING COUNT(WHOLENAME) > 1");


foreach( $duplicate_titles as $title ) {

    $post_ids = $wpdb->get_col( $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_title=%s AND post_date='".$title->post_date."'", $title->post_title ) ); 

    foreach( array_slice( $post_ids, 1 ) as $post_id ) {
        wp_delete_post( $post_id, true ); // Force delete this post
    }

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.