Deleting WordPress Posts With MySQL

WordPress only lets you view 20 posts at a time through the admin panel and that means that you can only delete 20 posts at a time. The fact is that there are times you may need to delete hundreds or even thousands of posts at a time and I can’t imagine taking a “20 posts at a time” approach to this. I often import WordPress posts and tonight, I did a 1200 post bulk insert into a WordPress database, only to discover that I made an error that required a re-import. Here’s some mysql queries that might help you in deleting WordPress posts.

Aside from the wp_posts table (which clearly holds the bulk of your WordPress post data), wp_term_taxonomy.term_taxonomy_id = wp_terms.term_id. We don’t really have to know this, as we can just look in the WordPress control panel to see what our category is. But.. it’s all part of understanding the table structure. If you delete posts directly from wp_posts, you’ll still see the posts count in your ‘categories widget’ as well as in the wp_term_taxonomy table because these other tables are holding the related data and WordPress still thinks you have posts out there.

SELECT * FROM ‘wp_term_relationships’ WHERE term_taxonomy_id =3

Tying the tables together….

select * from wp_term_relationships, wp_term_taxonomy
WHERE wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
AND wp_term_taxonomy.taxonomy = ‘category’ AND wp_term_taxonomy.term_id = 3

Let’s add the wp_terms table in:

SELECT * from wp_term_relationships, wp_term_taxonomy, wp_terms
WHERE wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
AND wp_term_taxonomy.taxonomy = ‘category’ AND wp_term_taxonomy.term_id = 3
AND wp_terms.term_id = wp_term_taxonomy.term_id

SELECT * from wp_term_relationships a
JOIN wp_term_taxonomy b ON b.term_taxonomy_id = a.term_taxonomy_id
JOIN wp_terms c ON c.term_id = b.term_id
WHERE
b.taxonomy = ‘category’ AND b.term_id = 3;

Alright.. let’s add the wp_posts table back in and delete all posts by category:

DELETE a,d — tables to delete from
FROM wp_term_relationships a
JOIN wp_term_taxonomy b ON b.term_taxonomy_id = a.term_taxonomy_id
JOIN wp_terms c ON c.term_id = b.term_id
JOIN wp_posts d ON d.id = a.object_id
WHERE
b.taxonomy = ‘category’ AND b.term_id = 3

Delete all posts and associated categories:

DELETE a,b,c,d — tables to delete from
FROM wp_term_relationships a
JOIN wp_term_taxonomy b ON b.term_taxonomy_id = a.term_taxonomy_id
JOIN wp_terms c ON c.term_id = b.term_id
JOIN wp_posts d ON d.id = a.object_id
WHERE
b.taxonomy = ‘category’

Hopefully, I got all the syntax right. I didn’t save it while I was doing it so this is from memory and it’s almost 2am. You can verify that it’s doing the job by creating a new category and then a single post and deleting from that category. The script should delete from multiple tables, so you’ll get more than 1 record affected, but the post will be gone and the category there. Let me know if see anything wrong (sleep deprivation is kicking in), or if you you have any questions.

1 Comment

  1. For the blog linked above:

    I’ve changed permalink structure and redirected using filesMatch

    I’m getting errors (Google WMT’s) for link structures that don’t exist. Some linked from link structures that don’t exist

    Mainly they look like wp/category/category-name/page/#1 thru 75 (or so)

    Can’t delete all categories in wp/admin

    My question is, can I delete category-tables in phpMyAdmin without losing posts and/or pages?

Submit a comment

CommentLuv badge

reverse phone lookupTattoo DesignsSEO
My New Amazon Script Is Close!
A new php script for sale is coming!
Sign up to get notified

..