Find and delete duplicate posts in WordPress

Recently a customer called me:

“We have duplicate posts on our Site, can you fix it?”

“Of course.” I said. And here’s how we did it.

The first and fastest (in other words: simplest) thing to do is to search for duplicate entries in the database. For example with the following SQL query.

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'

The result shows us all post that we have in our database, which share their title at least with one other post. To make the result faster and easier readable, our SQL statement only shows us the ID, post_title, post_type and post_status columns from the wp_posts table. Here are our fictional results.

ID post_title post_type post_status
12109 Aus dem Leben eines Taugenichts post publish
23 Aus dem Leben eines Taugenichts post publish
12123 Narziß und Goldmund post publish
121 Narziß und Goldmund post publish
2123 Narziß und Goldmund post publish
145 Jakob der Lügner post publish
12 Jakob der Lügner post publish

(Yes, these are all German language literature classics.)

You could now double check by performing a search for “Narziß und Goldmund” in the WordPress admin UI posts list screen. There you should get the same post duplicates as you’ve found by using the previous SQL query. As you’ll soon encounter, the reason why we choose to use the SQL admin UI over the WordPress admin UI is that we can search for all duplicate titles at once instead of running one search query for each title. And that is a huge time saver.

To proceed further with our task of cleaning out duplicates in our database, we only need to change two minor things: First we change SELECT to DELETE to actually remove the entries. And second, we switch from selecting only a few columns, that we needed for a quick glance at the duplicates, to all columns: *. Take a look at our new SQL 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 you run this query from your database UI (that would mostly be phpMyAdmin or Adminer) or a console, you’re mostly done. Keep in mind, we recommend that you back up your database before running such a query.

Author:

Hi. I am a media designer for digital and print media, with more than just a sense for forms, color and composition. I'm a father, husband, web developer, web designer, consultant, instructor, an IHK examiner for media designer, photographer and I ♥ WordPress.

Find René Reimann on www.rene-reimann.de⁠, ⁠, ⁠, and .

5 Comments

  1. BuckShank26.11.2013 09:32

    Thanks for that SQL. I had like 1700 dupes from a rss plugin.

    Reply

  2. Jayson T. Cote'25.01.2014 01:11

    Hey René, thanks so much for this snippet. It saved me a ton of time. For some reason I had a bunch of duplicate posts after exporting/importing a large db for one of my clients. I really appreciate you posting this, incredibly useful. Thanks again.

    Reply

  3. roel – 02.02.2014 15:55

    Thanks so much!
    Helped me clean 1000+ dup posts

    Reply

  4. evilest_n – 15.04.2014 14:20

    How would you run a query to find duplicates which have a meta_key the same?

    Reply

  5. Robin Majumdar15.04.2014 23:05

    Brilliant! Works far better than any plugin…

    Thanks for sharing your code!

    Reply

Leave a Reply

Your email address will not be published.