Query to change custom post type with specific category

Question

I need to change several hundred posts with custom post types to regular post type but only from one category within that custom post type.

So I have a custom post type 'films' and within that post type I have a couple dozen categories. What I would like to do is change all posts from custom post type 'films' that also have the category 'directors' to the standard post type without affecting other 'film' posts that do not have the category 'directors'.

I've searched around and have found a way to successfully change all posts within a post type using the query below but I need to be more specific.

UPDATE  `wp_posts` 
SET  `post_type` =  '<new post type name>' 
WHERE  `post_type` = '<old post type name>';

I've also tried using plugins such as 'Convert Post Types' but unfortunately they do not pull in categories from custom post types.

I've read a good deal of documentation on this but I'm totally new to this kind of work so I'm having a hard time wrapping my head around some of the syntax.

Answer 1

I would do it in php in two parts rather than one big raw sql statement, for safety reasons, eg in your "functions.php" put:

function wpse160706() {
    $old_post_type = 'films';
    $new_post_type = 'post';
    $category_slug = 'directors';
    $taxonomy = 'category';

    global $wpdb;
    $sql = $wpdb->prepare( 'SELECT p.ID FROM ' . $wpdb->posts . ' AS p'
        . ' JOIN ' . $wpdb->term_relationships . ' AS tr ON p.ID = tr.object_id'
        . ' JOIN ' . $wpdb->term_taxonomy . ' AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id'
        . ' JOIN ' . $wpdb->terms . ' AS t ON tt.term_id = t.term_id'
        . ' WHERE p.post_type = %s AND t.slug = %s AND tt.taxonomy = %s'
        , $old_post_type, $category_slug, $taxonomy );
    $post_ids = $wpdb->get_col( $sql );
    print("post_ids=" . implode( ',', $post_ids));
    foreach ( $post_ids as $post_id ) {
        $sql = $wpdb->prepare( 'UPDATE ' . $wpdb->posts
            . ' SET post_type = %s WHERE ID = %d AND post_type = %s'
            , $new_post_type, $post_id, $old_post_type );
        // Uncomment the following if happy that $post_ids correct.
        //$wpdb->query( $sql );
    }
}

then call it and make sure you're only getting the posts you want, then backup your database etc and uncomment the update query, run once and then remove the wpse160706() call.