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.
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