Query to change custom post type with specific category

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.

Related Posts: