0

I have tried the following phpMyAdmin query in an effort to delete all posts, from wordpress category 378, that are over 400 days old.

DELETE a,b,c,d
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

The query seems to have deleted that entire category, and all its posts, instead. How do I make a phpMyAdmin query that will just delete any posts over 400 days old, from that particular category.

I am looking for the exact query that I can use; so that all I need to do with an answer is ensure the category id is correct and, then, run it.

I do have database backups.

note: My wordpress database contains nearly 300,000 posts; so, I have encountered too many problems, which resulted in failure, trying to delete the posts from within the dashboard of wordpress.

  • I notice at least one error, it should be ... AND DATEDIFF( NOW(), a.post_date ) > 400, you missed the 'a.' before post_date – gmazzap Apr 23 '14 at 22:44
  • delete a,b,c,d 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(), a.post_date ) > 400 still deletes the entire category, and all its posts. Thankyou, though, @G. M. – q305noonce Apr 24 '14 at 0:06
  • I'd like to note that the missing a. before post_date isn't an error since post_date is unique to the wp_posts table and wouldn't be considered ambiguous. – phatskat Jan 26 '18 at 14:39
1

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;
  • 1
    What about the term count, wouldn't it go out of sync here? The table prefix can vary and It would be good to have a notice to take a db backup before testing ;-) The wp-cli might be an alternative. – birgire Jan 26 '18 at 18:00
  • I would definitely recommend taking backups, and OP said in their post that they are :) Also, this will definitely mess up term counts, but that should be expected when removing this kind of data directly in SQL. There are methods like wp_update_term_count_now that can be used to clean that up, and there are plugins and tools (like WP-CLI) that can also be used to fix term counts. – phatskat Jan 26 '18 at 18:03
  • ps: I was mainly thinking about possible future readers that might try to just copy/paste the SQL query, without reading the original question in detail and test it, forgetting to take a backup first ;-) – birgire Jan 26 '18 at 18:08
  • 1
    Fair enough, updating. – phatskat Jan 26 '18 at 18:10
-1

you have to add "AND a.post_type='post'"

delete a,b,c,d
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
AND a.post_type='post'
  • This only limits the query to deleting posts, however, it does not address the issue of the term being deleted from the category. – phatskat Jan 26 '18 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.