wordpress: SQL to clean posts, terms, relationships, taxonomy (and other associations) by post_title


Staff member
I need an SQL to clean my wordpress database by post_title.

For example, the following finds all posts from wordpress that contain the word "apple" in the title:

SELECT * FROM wp_posts WHERE post_title LIKE '%apples%';

And this deletes those same posts:

DELETE FROM wp_posts WHERE post_title LIKE '%apples%';

However, Im not sure how to delete all references in other tables by title was well because I don't know how they relate to the wp_posts table. I THINK you can remove rows related to posts from postmeta via the following AFTER you remove posts containing "apple":

DELETE FROM wp_postmeta WHERE NOT EXISTS (SELECT * FROM wp_posts WHERE wp_postmeta.post_id = wp_posts.ID)

This should work because after the initial SQL command, you now have rows in wp_postmeta without a relationship to the wp_posts table and removes said rows. Simple.

But I'm not sure how to remove the category and tag references because Im not sure of the relationships between wp_posts and wp_terms, wp_term_relationships or wp_term_taxonomy (in addition to any other table that could be effected by the first query).

Can someone help me here?

Thanks for all consideration.