SQL to update custom post taxonomies

I have a custom post “review”. It uses the same categories and tags from default posts, so they share “category” and “tag” taxonomies.

But later I changed the “review” register to have their own taxonomies, “review-category”, and “review-tag”. Of course all tags and categories previously assigned on reviews was unbind from them.

So the question: how can I (if possible) convert all tags and categories only in reviews to the new taxonomies? If I wasn’t clear enough:

UPDATE wp_term_taxonomy SET taxonomy='post-tag', parent=0 WHERE taxonomy='tag';

This can convert all “category” to “post-tag”, but I need to do it only for reviews. Something like (it doesn’t work):

SELECT FROM wp_posts WHERE post_type='review'(
UPDATE wp_term_taxonomy SET taxonomy='post-tag', parent=0 WHERE taxonomy='tag';
)

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You will need to join four tables to get categories of custom post type “review”

1. wp_term_relationships
2. wp_posts
3. wp_term_taxonomy
4. wp_terms

by using following query you can get list of categories applied to your custom post type:

SELECT *
FROM wp_term_relationships AS tr
LEFT JOIN wp_posts AS p ON tr.object_id = p.ID
LEFT JOIN wp_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
LEFT JOIN wp_terms AS t ON t.term_id = tt.term_id
WHERE p.post_type = 'review' AND tt.taxonomy = 'category';

and then update the type of these categories to your new custom category (i.e. “review-category”)

UPDATE wp_term_taxonomy SET taxonomy = 'review-category' WHERE term_taxonomy_id IN (1, 71, 72);

change where clause according to your category ids.

For more information visit following WordPress documentation links:
Database_Description
WordPress_Taxonomy

Method 2

You cant export only categories (or taxonomies generally). Just export all content with Tools/Export and you can delete content except categories from xml
try a look wp export all plugin

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0