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

3
votes

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.

1
sqlwordpresstaxonomyposts
I'm not conversant in the wordpress schema, but wouldn't it be post_id again? Although, if the tables have certain types of referential integrity enabled, removing the posts may remove all the related rows automatically (ie ON DELETE CASCADE). – Clockwork-Muse
tablename.post_id doesnt exist in the other tables, though it appears that wp_term_relationships.object_id corresponds to wp_postmeta.post_id and wp_posts.ID so that would seem to be the link between those two tables. Assuming my theory is true then DELETE FROM wp_term_relationships WHERE NOT EXISTS (SELECT * FROM wp_posts WHERE wp_term_relationships.object_id = wp_posts.ID) should work on THAT table as well after the first sql is run. – Erik Malson
Can anyone help me do both in one step...? – Erik Malson
I'm not aware of any RDBMS that allows you to run UPDATEs or DELETEs over multiple tables (ie - with a UNION). You're going to need to write some sort of script or stored procedure, supposing you can run it. Otherwise, just run them one-at-a-time... – Clockwork-Muse

1 Answers

0
votes

If your DB supports cascading, use it. The DELETE SQL you've proposed, or something similar, is last-gasp stuff. It's common for column names holding join data not to share names; the absence of tablename.post_id in other tables is unsurprising.