Quick and safe way to delete all entries with a given status?

Question

Due to a data grab import that went a bit awry I find myself with approx 10000 entries that I don't want! They've all got a specific status.

I'm currently deleting them in batches but the most I can do is 200 at a time and it's getting a bit boring.

Is there a quick and safe way to delete them all - perhaps using SQL (but which leaves things clean and tidy)?

Let's see if I can get an answer before I've finished manual deletion. :)

Answer 1

This SQL will clean up both exp_channel_titles and exp_channel_data:

DELETE exp_channel_titles, exp_channel_data 
FROM exp_channel_titles
INNER JOIN exp_channel_data
ON exp_channel_data.entry_id = exp_channel_titles.entry_id
WHERE exp_channel_titles.channel_id = 5 
AND exp_channel_titles.status = 'your_custom_status'

Replace the channel_id and status as appropriate. But as Jeremy's answer says, you might need to consider other tables!

A related discussion is in "What is the best way to delete every entry in a channel?"

Answer 2

Using SQL to remove entries means you also have to do extra clean up on relationship tables and categories.

You can bypass the input limits by appending the appropriate query string parameters to the address of the edit screen. For example, go to Publis > Edit > Your Channel. Then append the following to the url: &perpage=5&status=closed

You should get only 5 entries who's status is closed. You could change this to 500 I suppose to do what you're asking.

I would limit this to 500 or so to prevent the server from timing out.

-N

Answer 3

SQL seems like the way to go. The hardest part would be to make sure that any add-ons you're using that have their own tables of entries get wiped out too. (such as Playa, Matrix, etc)

Beyond those special cases, the only tables you really have to worry about are exp_channel_titles, exp_channel_data and exp_category_posts and exp_relationships if you're using relationships.

Answer 4

As suggested, SQL is the best way to do this in bulk.

The query is as follows:

delete exp_channel_titles, exp_channel_data
from exp_channel_titles
inner join exp_channel_data on exp_channel_data.entry_id=exp_channel_titles.entry_id 
where exp_channel_titles.status="MyCustomStatus"

NOTE: In my case I wasn't using any relationships or categories