Mysql delete all posts that have a given meta_key

Question

This question already has an answer here:

• How to delete from select in MySQL? 4 answers

I have a wordpress site that has some meta_key's asigned to my posts. I want to delete all posts in mysql that has the meta_key value acest_meta. With this SQL command :

SELECT `post_id`
FROM `wp_postmeta`
WHERE `meta_key` 
LIKE 'acest_meta' 
ORDER BY `wp_postmeta`.`post_id` ASC

i can see all the id's that contain that given meta_key but i don't have a delete check box neer. I see a message on top that say "Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available." How can i delete all those posts ?

Answer 1

Using Mysql query you can use join in delete query

DELETE p.*,w.*
FROM `wp_postmeta` w
JOIN wp_posts p
 ON(p.ID = w.`post_id`)
WHERE w.`meta_key` LIKE 'acest_meta'

Above query will delete all posts and its meta data contains a meta_key as acest_meta

Answer 2

you can delete with a select using an IN() statement and put the select in an IN like so

DELETE FROM `wp_postmeta` 
WHERE `post_id` IN 
(   SELECT `post_id`
    FROM `wp_postmeta`
    WHERE `meta_key` LIKE 'acest_meta' 
)

Answer 3

delete from wp_postmeta
 where post_id in
       (SELECT post_id FROM wp_postmeta WHERE meta_key = 'acest_meta')