post meta - How to delete duplicate records in wp_postmeta database table?

admin管理员组

文章数量:1324621

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.

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.

• post-meta
• mysql
• duplicates

Share Improve this question edited Sep 8, 2020 at 5:48 Jesse Nickles 73577 silver badges1919 bronze badges asked Mar 23, 2017 at 16:14 LeonardShelbyLeonardShelby 17511 gold badge44 silver badges1414 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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'

本文标签： post metaHow to delete duplicate records in wppostmeta database table