Mysql Query to Delete Duplicate Wordpress posts?

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
    }

}

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.