Delete duplicate record in wp_postmeta
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
- Identify records in wp_postmeta that have the same post ID and also have an entry for the meta key
- Delete all records identified in step 1 that do not have meta value of
So far, I have come up with this SQL to identify posts with two or more values for the same meta key
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
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.