Delete Duplicate Posts by Command Line SQL

  • Hello,

    I want to delete the duplicates in a category that have the same name. I have nearly 7K categories and 150K posts so manually is not an option. I found this code: 

    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'

    Which I paste into putty, however nothing happens.

    Will this code match the category and delete the posts with the same name? 

    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'

    I found this code here:
    https://wpkrauts.com/2013/find-and-delete-duplicate-posts-in-wordpress/

Viewing 1 replies (of 1 total)
  • Thread Starter gward2

    (@gward2)

    I’ve also tried this: 

    mysql -ppassword_of_user -u user_name -e
'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''
database_name
Viewing 1 replies (of 1 total)
  • The topic ‘Delete Duplicate Posts by Command Line SQL’ is closed to new replies.