Batch-deleting Wordpress posts?

July 20, 2010 7:26 AM

MySQL/Wordpress question: Help me delete all posts from a certain category before a certain date.

So we want to do some cleaning, because we have an exceptionally high number of posts in a specific Wordpress category. I'd like to be able to delete, say, every post in category 9 with a date of 1/1/2010 or earlier.

I have PHPMyAdmin access, but am pretty unfamiliar with it. As such, I'm kinda skeezed out about doing anything that might delete more than I want. Is this easily doable in PHYMyAdmin, or will I need to hire someone to write a plug-in?

posted by jbickers to Computers & Internet (8 answers total)

Wordpress can do this inside the panel.

1. Log into wordpress and go to Posts
2. Select the date and category from the drop down menus at the top of the page. Click the filter button.
3. Select all the posts by clicking the square check box next to the word Title before all the posts.
4. In Bulk Actions drop down menu, select move to trash
5. Hit the Apply button.
posted by royalsong at 7:36 AM on July 20, 2010

Response by poster: I would love that, royalsong, but we're talking about 60,000+ posts over the course of three years. That screen only lets you delete 20 at a time.

Unless I'm missing a setting somewhere?
posted by jbickers at 7:41 AM on July 20, 2010

Response by poster: Dude, just answered my own question ... "Screen options" at the very top of the dashboard lets you pick how many get displayed.

Perfect ... thanks!
posted by jbickers at 7:41 AM on July 20, 2010

Response by poster: OK, so another snag ... trying to do a bunch at one time yields this error:

Request-URI Too Large

The requested URL's length exceeds the capacity limit for this server.

... because of the way Wordpress forms it's "send to trash" URLs. So I guess this takes me back to the database command line drawing board?
posted by jbickers at 7:48 AM on July 20, 2010

If you want to try the SQL route, the following four queries should delete the posts you want to delete, plus any comments attached to them:

  DELETE FROM wp_commentmeta
  WHERE comment_id IN (
    SELECT comment_ID FROM wp_comments
    WHERE comment_post_ID IN (
      SELECT ID FROM wp_posts 
      WHERE post_category = 9 AND post_date < '2010-01-01'));

  DELETE FROM wp_comments
  WHERE comment_post_ID IN (
    SELECT ID FROM wp_posts 
    WHERE post_category = 9 AND post_date < '2010-01-01');

  DELETE FROM wp_postmeta
  WHERE post_id IN (
    SELECT ID FROM wp_posts
    WHERE post_category = 9 AND post_date < '2010-01-01');

  DELETE FROM wp_posts
  WHERE post_category = 9 AND post_date < '2010-01-01';

WARNING: This will permanently delete the relevant posts and comments. There is no recovering the data you delete this way. BACK UP YOUR DATABASE BEFORE YOU DELETE ANYTHING.

Make sure you review these queries closely to make sure you understand what they're doing. The basic idea is that you are using nested SELECT statements to tell the database which rows to delete. Each of those nested SELECT statements includes a WHERE clause (WHERE post_category = 9 AND post_date < '2010-01-01') so that you're only deleting records related to the posts you're trying to delete.

You should also make sure these queries match the structure of your database tables. I'm basing this on Wordpress 3.0, but your tables may be slightly different if you're using an older version.

I haven't included any queries for deleting category and tag metadata for the posts you're going to delete. To be consistent and to avoid having useless junk records in your database, you really ought to clean up those tables too. And if you've installed any plugins that add their own tables to the database, you may also need to delete stuff from those tables.
posted by twirlip at 11:20 AM on July 20, 2010

There is a wordpress Bulk-Delete plugin. Would that do the job?
posted by Kerasia at 1:35 PM on July 20, 2010

Response by poster: Kerasia, that looks like it would work perfectly ... but this has me scared.
posted by jbickers at 1:47 PM on July 20, 2010

You should be extremely cautious doing deletes like this, but as long as you back up your database first and can restore if need be, it's all good.

Have you considered rather than doing a delete, instead just exporting as an xml file what you want to keep, then reinstall WP and install that import file. That would be another valid approach.
posted by artlung at 4:28 PM on July 20, 2010