How to empty wordpress custom post Database table

Question

I have around 2000 custom posts. And 7 regular wp pages. I want to delete all those custom post without effecting the pages. I thought the easiest way would be to empty the custom post table but I dont know how to do it. I am familiar with DB operations but I dont know the schema of wp posts, so have no idea where these custom posts are located. I don't mind using a plugin to do it.

Inshort:- Just want to delete all the custom posts and their data.

Answer 1

I know this is an old question, but given that it came up first when Googling this issue I figured it should have a solution attached.

To delete all posts of a given type, access your site's database using phpMyAdmin, the command line, or whatever you prefer. Then execute the following commands:

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);

The first line deletes all posts with that post_type from wp_posts.

The second then removes any post metadata from wp_postmeta that is no longer tied to a post.

The third line removes and tags or categories from wp_term_relationships that were tied to that post type. This line should be omitted if you intend to create more posts of the given post_type in the future.

Answer 2

I used a custom page template to delete all the post from the wordpress database. Everytime I would go to this page it would delete 300 posts.The code for the page template is:

<?php
// Get 300 custom post types pages, set the number higher if is not slow.

$mycustomposts = get_posts( array( 'post_type' => 'movies', 'numberposts' => 300));
    echo '<pre>';
    print_r($mycustomposts);
    echo '</pre>';
   foreach( $mycustomposts as $mypost ) {
     // Delete's each post.
    wp_delete_post( $mypost->ID, true);
    // Set to False if you want to send them to Trash.
   }
   echo '<h1 style=:"color:red;"> DELETED! DELETED! DELETED! DELETED! </h1>';



// 300 custom post types are being deleted everytime you refresh the page.?>

Answer 3

Posts, pages, and custom post types are all stored in the same table (usually wp_posts unless you set a custom prefix).

You can't just clear out the table without deleting your pages as well. The most straight-forward way to do this (without hurting your database) is to delete them manually through the WordPress Admin.

Yes, it will take a while. But remember, posts (and pages and custom posts) also store data in the wp_postmeta table. If you delete them through the Admin UI, WordPress will clear up the meta table as well. You'll save yourself a lot of trouble if you just do things through the UI rather than try to do it manually through the DB.

Answer 4

This will delete the post, the post metadata, and any terms used on the post. It will not delete the terms themselves. The PHP script is similar to this but slower.

Replace {{your CPT}} with the identifier for your custom post type and pop this in your database on phpmyadmin or mysql.

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}}';

Unfortunately it won't delete related media entries. It would be dangerous to do that using sql or even php since you might delete media entries that are linked to multiple posts.

All the best.