How Do I Delete WordPress Posts Older Than 400 Days, From A WordPress Category

Question

Addition note and warning: always always always take backups before doing anything in your database. It’s very easy to do something you didn’t mean to and lose tons of data, especially when dealing with DELETE statements. Also, if your database is using InnoDB for the database engine, you can use transactions via BEGIN, ROLLBACK, and COMMIT.

Your query is close, but you’re specifying d in your DELETE clause which will result in the rows in wp_term_taxonomy to be deleted as well. wp_term_taxonomy is what relates your term to a taxonomy, which is why the term is removed from the category. Try this instead:

DELETE
    a, -- DELETE from posts
    b, -- DELETE from term relationships
    c  -- DELETE from postmeta
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id )
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
LEFT JOIN wp_terms e ON ( e.term_id = d.term_id )
WHERE e.term_id = 378 AND DATEDIFF(NOW(), post_date) > 400;

It helps to understand how WordPress stores information. Whenever you create or update a post, there are three tables that are affected:

wp_posts Pretty obvious, this is where WordPress stores the primary record for the post.
wp_postmeta Where WordPress stores extra information related to the post.
wp_term_relationships Defines how post-type objects are related to terms.

These are the tables that you should be focused on when you want to remove a post and all of its relationships.

A final note: it helps if you use alias names that represent their table names, it helps queries that are complex such as this to be easier to understand when something goes wrong or just when you don’t look at the query for a while and then come back to it. An example of your query written with more descriptive aliases would be:

DELETE
    p, -- DELETE from posts
    tr, -- DELETE from term relationships
    pm  -- DELETE from postmeta
FROM wp_posts p
LEFT JOIN wp_term_relationships tr ON ( p.ID = tr.object_id )
LEFT JOIN wp_postmeta pm ON ( p.ID = pm.post_id )
LEFT JOIN wp_term_taxonomy tt ON ( tt.term_taxonomy_id = tr.term_taxonomy_id )
LEFT JOIN wp_terms t ON ( t.term_id = tt.term_id )
WHERE t.term_id = 378 AND DATEDIFF(NOW(), post_date) > 400;

Related Posts: