4

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.

Erik Malson
  • 117
  • 2
  • 11
  • 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 Jul 16 '12 at 15:36
  • **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 Jul 17 '12 at 13:41
  • Can anyone help me do both in one step...? – Erik Malson Jul 17 '12 at 13:42
  • I'm not aware of any RDBMS that allows you to run `UPDATE`s or `DELETE`s 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 Jul 17 '12 at 15:35

1 Answers1

0

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.

David Healy
  • 83
  • 4