0

I have been trying to figure out how to delete duplicate posts in my Wordpress database. More specifically, I am looking to delete all wp_posts that have the same meta_value for the meta_key "itemID" in the wp_postmeta table.

In other words, I don't want 2 or more posts to have nor the same title, nor the same "itemID" meta_value, but I'm not sure how to go about this.

Just for reference, the queries I usually use are below.

With the first query below, I can see what meta_values are repeated, and how many times. Additionally with the second query, I can see what posts are duplicates, only by their title (I did not come up with this one)

1.

SELECT meta_id, post_id, meta_key, meta_value, COUNT( * ) 
FROM wp_postmeta
WHERE meta_key =  '_itemID_'
GROUP BY meta_value
HAVING COUNT( * ) >1

2.

SELECT bad_rows.*
from wp_posts as bad_rows
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 good_rows on good_rows.post_title = bad_rows.post_title
and good_rows.min_id <> bad_rows.id

Thank you for looking and merry xmas!

OSK
  • 157
  • 1
  • 2
  • 10

1 Answers1

0

for records with duplicate title you can simply do this :

DELETE FROM wp_posts 
WHERE ID NOT IN ( SELECT id FROM(SELECT id,title FROM wp_posts GROUP BY title) )

and for meta key word you should first join two tables and do the same.

Majid Abdolhosseini
  • 2,191
  • 4
  • 31
  • 59