How to Delete Duplicate Posts from WordPress

One of the biggest problems users get stumped by is the problem of duplicate posts. Some users who have 30K+ posts on there blog and they wish to delete the duplicate posts, it can be really taxing for them to go through the list of posts from wp-admin and delete each post one by one.
There are a number of plugin which help in this, namely :

- Duplicate Posts
- Delete Duplicate Posts
- Delete all Suplicate posts

Other than these and many other plugins, one of the best and quickest ways to delete duplicate posts is via Database query.

Note : Before running any queries on your database, please make a backup of the database, in case anything goes wrong.

First lets write a query to display all the duplicate posts on your WordPress blog :

SELECT a.ID, a.post_title, a.post_type, a.post_status
FROM wp_posts AS a
   INNER JOIN (
      SELECT post_title, MIN( id ) AS min_id
      FROM wp_posts
      WHERE post_type = 'post'
      AND post_status = 'publish'
      GROUP BY post_title
      HAVING COUNT( * ) > 1
   ) AS b ON b.post_title = a.post_title
AND b.min_id <> a.id
AND a.post_type = 'post'
AND a.post_status = 'publish'

This query will display a table of all the duplicate posts present on your blog.
Go through this table to check if it is working correctly or not.

To delete all the duplicate posts, we use the following query :

DELETE a.*
FROM wp_posts AS a
   INNER JOIN (
      SELECT post_title, MIN( id ) AS min_id
      FROM wp_posts
      WHERE post_type = 'post'
      AND post_status = 'publish'
      GROUP BY post_title
      HAVING COUNT( * ) > 1
   ) AS b ON b.post_title = a.post_title
AND b.min_id <> a.id
AND a.post_type = 'post'
AND a.post_status = 'publish'

After running this query, all the duplicate posts from your blog should have been deleted.