Bulk delete WordPress posts with phpMyAdmin

Question

I have over 8,000 posts generated by wp-o-matic and I want to get rid of all of them.

I want to execute this query in phpMyAdmin. I just want to make sure this is safe to run and it's not wiping out my pages. This is the query:

DELETE FROM 'wp_posts' WHERE 'post_type' = 'post'

Thanks

Answer 1

The join isn't that complex, but I wouldn't bother with it. Since you have a lot to remove a direct SQL query will run a LOT faster than WP.

However, we do need to keep track of the post ID numbers and remove the descendants and metadata.

Not tested / Make a backup / your risk etc. etc.

create temporary table `cull` (`post` int);

insert into cull select ID from wp_posts where my_criteria = 'my value';

delete from wp_posts where ID in ( select post from cull );
delete from wp_posts where post_parent in ( select post from cull );
-- done with temp table, cleanup metadata
delete from wp_postmeta where post_id not in ( select ID from wp_posts );
delete from wp_comments where comment_post_id not in ( select ID from wp_posts );
delete from wp_commentmeta where comment_id not in ( select comment_ID from wp_comments );
delete from wp_term_relationships where object_id not in ( select ID from wp_posts );

Answer 2

Don't try to do this with SQL. There is post information is multiple tables. You will end up with orphan data scattered throughout the database. While it is possible to write the JOINs you'd need to delete everything, the SQL could be complex.

Create a simple Loop and use the Core to delete your posts.

$args = array( 
  'post_type' => 'post',
  'ignore_sticky_posts' => true,
);

$del = new WP_Query( $args );

while ($del->have_posts()) {
  $del->the_post();
  wp_delete_post( $post->ID, false ); 
}

Set the second argument to wp_delete_post() to true to bypass the "trash" and go straight to "permanently, irretrievably gone".

Answer 3

If you know the sequence of the IDs to be deleted (e.g. from 12001 to 23006) and YOU ARE SURE(!) there is no post you'd like to keep, with and ID between the first/last IDs to be deleted.

Example: a plugin--e.g. a sitemap plugin--generated thusands of posts and you know that all posts betweeen ID 12001 and 23006 should be deleted, you could run:

DELETE FROM wp_postmeta WHERE post_id in (SELECT ID FROM wp_posts WHERE ID BETWEEN 12001 AND 23006);

DELETE FROM wp_posts where ID BETWEEN 12001 AND 23006;