Updating custom taxonomy name – MySQL Find and Replace.

Custom taxonomy is used to bring order to new WordPress post types. Similar to standard post categories, a portfolio post type might have a ‘project’ taxonomy, a staff profile post a ‘role’ or ‘department’ taxonomy. But what happens when the custom taxonomy you registered is no longer suitable?

An example of Custom Taxonomy

public function register_taxonomy() {
		// Labels
		$singular = 'Role';
		$plural = 'Roles';
		$labels = array(
			'name' => _x( $plural, "taxonomy general name"),
			'singular_name' => _x( $singular, "taxonomy singular name"),
			'search_items' =>  __("Search $singular"),
			'all_items' => __("All $singular"),
			'parent_item' => __("Parent $singular"),
			'parent_item_colon' => __("Parent $singular:"),
			'edit_item' => __("Edit $singular"),
			'update_item' => __("Update $singular"),
			'add_new_item' => __("Add New $singular"),
			'new_item_name' => __("New $singular Name"),

		// Register and attach to 'team-profile' post type
		register_taxonomy( strtolower($singular), 'team-profile', array(
			'public' => true,
			'show_ui' => true,
			'show_in_nav_menus' => true,
			'hierarchical' => true,
			'query_var' => true,
			'rewrite'  => array( 'slug' => 'roles' ),
			'labels' => $labels
		) );

You can alter the taxonomy name in your register_taxonomy() function, but if you already have posts assigned to the old taxonomy name, they won’t update.
In this example I had changed the taxonomy from department to role and lost 300 team-profile posts that had been assigned to it. The taxonomy section was now blank within WordPress, without the taxonomy entries, descriptions and assigned posts.

Log into phpMyAdmin – Find your old Taxonomy References

Search for your old taxonomy name

Within phpMyAdmin you can use the search option at the top (exact phrase, within all tables) to track down where your old taxonomy name is used.

You should find references in both your wp_term_taxonomy and wp_postmeta tables. Make a note of the exact taxonomy name as you can now use the SQL below to replace it with your new taxonomy value.

Explore Distinct values

If you have no idea what your old taxonomy value is. Go to your wp_term_taxonomy table and click on structure and look for the taxonomy row. Select the Distinct values option on the far right. This will bring up your taxonomy values in a table, with your custom values most likely at the bottom.

MySQL to update the custom taxonomy name in WordPress

Custom Taxonomy Find and Replace

update the custom taxonomy name in WordPress

As always, make sure to do a database backup, before running any MySQL scripts. Double check that you’re targeting the correct database and have the correct old and new taxonomy values.

UPDATE `wp_term_taxonomy` SET `taxonomy` = 'new-value' WHERE `taxonomy` = 'old-taxonomy-name';
UPDATE `wp_postmeta` SET `meta_value` = 'new-value' WHERE `meta_value` = 'old-taxonomy-name';

Put your taxonomy values (old and new) in the code above. After executing this script your custom taxonomy items should reappear within your WordPress CMS.

Let me know if this was helpful for your WordPress website in the comments below.


Leave a comment