Mysql Query to Delete Duplicate WordPress posts?

2 comments

1. 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.

2. 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
       }
   
   }
   

Comments are closed.