0

Some of my posts have two values for a meta key called cb_full_width_post. I want to only keep the records that have a meta value of nosidebar-narrow. This means I need to

  1. Identify records in wp_postmeta that have the same post ID and also have an entry for the meta key cb_full_width_post
  2. Delete all records identified in step 1 that do not have meta value of nosidebar-narrow

So far, I have come up with this SQL to identify posts with two or more values for the same meta key cb_full_width_post:

SELECT post_id, meta_key, count(*)
FROM wp_postmeta
WHERE meta_key = 'cb_full_width_post'
GROUP BY post_id, meta_key
HAVING COUNT(*) > 1

This returns a result set of wp_postmeta records having more than one value for cb_full_width_post.
I'm stuck on the next step. I need to use the result set from the above query, which gives me the post IDs that I need to target. I need to find those post IDs in wp_postmeta, and delete all records where meta_value != 'nosidebar-narrow'

Edit: some visual info

Record #1
post_id:     1  
meta_key:    cb_full_width_post   
meta_value:  Good

Record #2
post_id:     1
meta_key:    cb_full_width_post
meta_value:  Bad_if_duplicate

Record #3
post_id:     2
meta_key:    cb_full_width_post
meta_value:  Bad_if_duplicate

I only want to delete Record #2, because it has meta_value "Bad_if_duplicate" and because it is a duplicate (same post ID as Record #1). Even though Record #3 has meta_value "Bad_if_duplicate", I don't want to delete it, because that is its only meta_value, i.e. it's not a duplicate.

0

You don't need a select statement to do this, a DELETE statement can handle finding the records. Make sure to backup your database first, then try the following SQL query.

DELETE FROM wp_postmeta
WHERE meta_key = 'cb_full_width_post'
AND meta_value != 'nosidebar-narrow'
  • 1
    That will delete all records with meta_value != 'nosidebar-narrow'. It doesn't just target posts that have duplicates. There are some records that have meta_value != 'nosidebar-narrow' that I need to keep. – LeonardShelby Mar 23 '17 at 16:24
  • @LeonardShelby I'm confused, are you trying to delete the whole post and all associated meta (from wp_posts and wp_postmeta tables) or just the meta values? When you say "I want to only keep the records that have a meta value of nosidebar-narrow" that's exactly what the above query does. – brianjohnhanna Mar 23 '17 at 18:34

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.