At some point or another, you may want to adjust the structure of categories in your WordPress blog. I just ran into such a situation when a client who was previously managing two blogs (one for “News” and one for industry “Ramblings”) wanted to merge their two blogs into a single blog. Using WordPress and a little bit of SQL, I’m going to teach you how to move posts in bulk to a new category. This tutorial assumes that you are using the default “wp_” table prefix, so make adjustments as neccessary; and as always, create a backup of your database before trying any of the steps below.
Step 1: Create the New Category
This step is easy. Simply create the new category as you normally would from within WordPress. After the category has been created, issue the following SQL against your WordPress database to determine the ID of the new category:
SELECT * FROM WP_Terms WHERE category_name = "New Category";
For me, this command told me that the id of my new category was 46, so I’ll remember that and continue to the next step.
Step 2: Find the ID of the Old Category
Now we need to do the same thing, but for the old category. In my case I am moving all Uncategorized posts to the category I just created, so I need to find the unique ID of the “Uncategorized” category:
SELECT * FROM WP_TERMS WHERE CATEGORY_NAME = "Uncategorized";
This command tells me that “Uncategorized” has a unique id of “1”, so I’ll remember this value and continue.
Step 3: Update the Post/Category Relationship
In this step we’re finally modifying our database, so make sure you have a backup of your WordPress database before you continue. We’re now going to update the relationship between the posts and the category. The WP_TERM_RELATIONSHIPS table contains a list of post-to-category relationships. The OBJECT_ID field specifies the unique id of the posts, the TERM_TAXONOMY_ID field specifies the unique ID of the category. So what we need to do is update any record with TERM_TAXONOMY_ID equal to the value from Step 2 to the ID of the table we created in Step 1:
UPDATE WP_TERM_RELATIONSHIPS SET TERM_TAXONOMY_ID = 46 WHERE TERM_TAXONOMY_ID = 1;
And voila! Your posts will now be treated as members of the new category.