0

I need to delete all posts that have a certain meta value for a certain meta key. I am looking for how to do this directly in the DB. So for instance, I have a meta field called "state", and I want to delete all posts (and there are thousands) whose meta value for that key is NOT equal to "CA". I have found queries that delete all posts of a certain type and all associated meta data, but can't find one that will let me delete posts BY metadata.

  • I can do this now with two separate queries, but it would be nice to be able to do it in one. Still, this is progress. The first query is like this: DELETE wp FROM wp_posts wp LEFT JOIN wp_postmeta pm ON pm.post_id = wp.ID WHERE pm.meta_key = 'state' AND pm.meta_value != 'CA'; And the second (to clean up the orphan metadata) is like this: DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL If no one replies with a single query I will mark this as my own answer I suppose. – Stephen Sep 9 '13 at 22:24
  • 4
    There is a way to JOIN deletes but 1) pure SQL questions are (tend to be considered) off topic here, and 2) I would not recommend doing this in pure SQL anyway. It is a lot safer to pull your data, process it with PHP, and delete with Core functions. – s_ha_dum Sep 9 '13 at 23:34
2

from my comment above:

I can do this now with two separate queries, but it would be nice to be able to do it in one. Still, this is progress. The first query is like this:

DELETE wp FROM wp_posts wp LEFT JOIN wp_postmeta pm ON pm.post_id = wp.ID WHERE pm.meta_key = 'state' AND pm.meta_value != 'CA';

And the second (to clean up the orphan metadata) is like this:

DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL

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.