wordpress mysql - delete all custom post type

Question

I've got a wordpress/mysql set up using the 'custom post type' which currently holds about 40,000 rows of data in the wp_posts table of the database. I'm trying to work on an SQL query that will remove all the rows of a certain custom post type in one go but it keeps telling me I've got a syntax error. The code I'm using is below, apologies if it's simple but SQL is alien to me and I'm struggling to find anything in the forums.

DELETE FROM `wp_posts`
WHERE  `post_type` =  'prefix-numberplates'
LIMIT 0 , 3000

There are ` around wp_posts & post_type but it won't let me style them on here. Any help is greatly appreciated.

Solution 1

Solved -

DELETE FROM `wp_posts`
WHERE  `post_type` =  'prefix-numberplates'

worked - took the bottom section out & it was fine.

Solution 2

global $wpdb;

// delete all posts by post type.
$sql = 'DELETE `posts`, `pm`
    FROM `' . $wpdb->prefix . 'posts` AS `posts` 
    LEFT JOIN `' . $wpdb->prefix . 'postmeta` AS `pm` ON `pm`.`post_id` = `posts`.`ID`
    WHERE `posts`.`post_type` = \'prefix-numberplates\'';
$result = $wpdb->query($sql);

This will delete from both posts and postmeta table. It left no junk in db.

Solution 3

The following removes from the posts, postmeta and term_relationships table:

DELETE p,tr,pm
    FROM wp_posts p
    LEFT JOIN wp_term_relationships tr
        ON (p.ID = tr.object_id)
    LEFT JOIN wp_postmeta pm
        ON (p.ID = pm.post_id)
    WHERE p.post_type = 'post_type_name';

Just change 'post_type_name' to the cpt you wish to remove. Also change table name prefixes where appropriate.

Solution 4

This way makes sure you get the right table - $wpdb knows best :)

// delete CPT posts ##
global $wpdb;

$posts_table = $wpdb->posts;

$query = "
  DELETE FROM {$posts_table}
  WHERE post_type = 'post_type_name' 
";

$wpdb->query($query);

View more solutions