2

We imported over 250K posts into our multi-author WordPress database but have found duplicate posts where the same author has multiple posts with the same slug. Could you please recommend a MySQL select statement that returns all records where a post_author has multiple records with the same post_name? Thank you.

  • 1
    Try SELECT p.ID, p.post_author, p.post_name, p.post_type, p.post_status FROM wp_posts p INNER JOIN wp_posts p2 ON p2.post_author = p.post_author WHERE p.ID != p2.ID AND p.post_name = p2.post_name LIMIT 3 - this queries for all posts regardless their post type and status; hence I selected the post ID, author, slug, type and status so that you can inspect the results before doing anything further. The results are also limited to 3. (If necessary, change the table prefix - i.e. wp_) – Sally CJ Nov 9 '18 at 3:20
  • @SallyCJ, this select statement helped us greatly in identifying all our duplicate posts. If I could ask you to take it one step further, how would we create a list that includes records to delete? In order words, the select statement above returns the original record and their duplicate records (some posts were duplicated more than once). Is there a way to select only the duplicate records so we can easily run those records through a delete process while still retaining the original record? – HBCondo Nov 9 '18 at 10:06
  • 1
    Not sure if you still need it, but try this - make sure to read the comments. And as a disclaimer, I'm not responsible for any data loss due to using that code.. I've tested it, but only with a few records - and dummy records. So basically, backup your database and don't perform DELETE operations until you're 100% certain of what you're doing.. and that's not at all a joke... – Sally CJ Nov 9 '18 at 18:14
  • No problem buddy. Glad it helped you. :) – Sally CJ Nov 12 '18 at 0:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.