Mysql delete all posts that have a given meta_key

Question

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 ?

score 4 · Answer 1 · answered Aug 23 '14 at 22:48

4

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' 
)

answered Aug 23 '14 at 22:48

John Ruddell

24,127
5
51
83

score 3 · Accepted Answer · answered Aug 23 '14 at 22:49

3

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

answered Aug 23 '14 at 22:49

M Khalid Junaid

62,293
9
87
115

1

+1 for a different way to do it with a join :) – John Ruddell Aug 23 '14 at 22:53

score 2 · Answer 3 · answered Aug 23 '14 at 22:48

2

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

answered Aug 23 '14 at 22:48

Brian DeMilia

12,688
1
20
31

same for you :) we had the same idea :) – John Ruddell Aug 23 '14 at 22:57

Mysql delete all posts that have a given meta_key

3 Answers3

Linked