I'm looking for a safe and fast way to delete all of one custom post type's posts. Using get_posts() and wp_delete_post() for each returned post does not work; it's not fast enough due to the sheer amount of database queries involved (timeout error).

Preferably, I'm looking for a single database query to run that deletes all posts that are of a custom post type. Any thoughts?

  • Is this a one-time event? If so, quick sql query via phpMyAdmin seems easiest. If this is a house-keeping step needed to be done programmatically/repeatedly, that won't help you. – jdm2112 Nov 13 '15 at 21:53
  • It needs to be done regularly, without manual database access, unfortunately. – Marcus McLean Nov 13 '15 at 22:03
  • Roger that. I would recommend looking at wpdb class then. Preferred method and "the WP way" for working directly with the DB. Let us know if you need help with that query. I can post a full answer later if needed codex.wordpress.org/Class_Reference/wpdb – jdm2112 Nov 13 '15 at 22:14
  • 2
    Use 'fields' => 'ids', in get_posts to get only post ID. This is all you need and it will significantely speed up your query – Pieter Goosen Nov 14 '15 at 3:09
  • 1
    @MarcusMcLean : obviously, you missed my point ;) A single post has data in the posts table, the postmeta table, the taxonomy table(s), and possibly the options table. Any pure SQL query you write to delete those posts will be quite complicated. You stand a good chance of leaving things behind in those various tables. I would try deleting, say, 50 at a time at 5 minute intervals using the Core functions and wp_cron() – s_ha_dum Nov 14 '15 at 4:08

You can delete all post via $wpdb

DELETE FROM wp_posts WHERE post_type='post_type';
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT id FROM wp_posts);
DELETE FROM wp_term_relationships WHERE object_id NOT IN (SELECT id FROM wp_posts)

or use this query replace it with {{your CPT}} with your Custom Post Type

DELETE a,b,c
    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)
    WHERE a.post_type = '{{your CPT}}';
| improve this answer | |
  • 1
    this would probably work fine, but don't forget about term counts in one of the term tables. WordPress should have a function to update this. – Joel M Feb 28 '18 at 19:37
  • users also have post counts on users screen, not sure if this data is saved beforehand or calculated on page load. – Joel M Feb 28 '18 at 19:39
  • 1
    @JoelM The count column exists in the database table wp_term_taxonomy. It seems like the function you are looking for to update the count is wp_update_term_count($terms, $taxonomy, false) or wp_update_term_count_now($terms, $taxonomy) defined in wp-includes/taxonomy.php. – Ken Jul 24 '19 at 11:46

You can delete all posts of a custom post type in various methods but here I am gonna show you how to do this without using SQL query. Here, for example, our post type is product

$allposts= get_posts( array('post_type'=>'product','numberposts'=>-1) );
foreach ($allposts as $eachpost) {
wp_delete_post( $eachpost->ID, true );

See full tutorial Reference Here

| improve this answer | |
  • Read the OP again: "Using get_posts() and wp_delete_post() for each returned post does not work; it's not fast enough due to the sheer amount of database queries involved (timeout error)." You are using the two functions he doesn't want to use. – Mike Nov 2 '18 at 8:19

This can now be done with the WordPress CLI using wp post delete. Once the CLI is installed, the following terminal command (while in your site's root directory) will delete all posts of type mycustomtype:

wp post delete $(wp post list --post_type='mycustomtype' --format=ids)

No raw SQL (*shudder*), no worrying about timeouts, and it's fast. For example, I just deleted ~2500 posts in less than two minutes.

| improve this answer | |

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.