WordPress: SQL query to add or remove categories on multiple posts A set of useful MySQL queries to perform bulk operations against the WordPress Database such as adding and/or removing categories on multiple posts

10 Must-Have features for a WordPress Website in 2019

In this article I will try to explain how to perform a series of operations on the WordPress categories associated with articles using SQL queries that act directly on the database.

The sample queries have been written for a MySQL database: however, they should work - with a few minor changes - on any other WordPress-compatible DB Engine as well.

Preliminary tasks

Needless to say, since we're going to perform some permanent operations to our WordPress DB, it is definitely advisable to run a complete backup of all the tables that we are going to modify (or of the entire database), so that we'll be able to recover from SQL errors and/or unwanted changes.

If you need a tool to accomplish this, we strongly suggest you to use either the official MySQL Workbench or SQLyog, a simple and free solution that allows you to backup individual tables and / or entire databases through a practical user interface and with a few clicks.

Wordpress: SQL query to add or remove categories on multiple posts

Wordpress: SQL query to add or remove categories on multiple posts

both of these tools are available in both Windows and Linux versions and will also allows you to make the queries that we're going to illustrate in the following paragraphs using a handy and convenient GUI interface, thus eliminating the need to use the command-line.

However, if you like the command-line, you can perform the backup using the mysqldump console tool: if you need info about that tool you can check out the official guide.

Posts, Categories and Relationships

Before taking a look at the queries, it is advisable to spend a couple minutes to understand how WordPress organizes the various posts and categories within its database. As you probably already know, WordPress does use a relational database model (RDBMS): more specifically, posts and categories are linked together using a many-to-many relationship; this means that each post can be associated with multiple categories and, conversely, each category can be related with multiple posts.

The posts are stored in the wp_posts table: this means that, to retrieve a specific post, you need to execute the following SQL query:

It goes without saying that the @ID placeholder must be replaced with the post ID we want to retrieve.

Categories works in a slightly different (and more complex) way: they are stored in the wp_terms table, together with other "terms" of different types (taxonomies);  they can be identified as categories because they do have the category taxonomy, but such information is stored in a different table (wp_term_taxonomy). This means that, if we want to retrieve all the categories, we'll need to execute the a SQL query with a JOIN statement in the following way:

Let's now address the many-to-many relationship between posts and categories: such info is stored in the wp_term_relationships table, which contains the object_id field (the post_id) and the term_taxonomy_id (the term_id of the category). This means that, in order to retrieve all the categories of a specific post, we can execute the following SQL query:

Replacing the @PostID placeholder with our post's post_id.

Now we know everything we need to perform some useful INSERT and/or DELETE queries to add and/or remove some categories to our posts.

Adding a new Category to all Posts

Let's assume that we have just created a "News" category, and we want to add it to all the articles: although we could definitely do that using GUI by logging to the WordPress administration panel, such technique would be very slow (and error prone) if our WordPress site had a lot of articles.

Here is how we can solve the problem with a single SQL query:

The above query works in a rather simple way: it retrieves all the posts from the wp_posts table (minus those who are already associated with the @CategoryID) and creates an entry in the wp_term_relationships table for each one of them. Needless to say, the @CategoryID must be replaced with the actual ID of the category we want to add.

Adding a new Category to all Posts with an existing Category

Let's now see how can we add an additional category to all post that are already associated with another, already existing category. Such task can be performed with the following SQL query:

As we can see, we'll have to replace the @CategoryID with the ID of the additional category we want to add, and the @OldCategoryID placeholder with the existing category ID.

The above query follow the same approach of the previous one, with the following key difference: the IDs of the posts that we need to add the new category to are not retrieved by querying the wp_post table; they are fetched from the wp_term_relationships instead. We are allowed to do that because we know they must be there, since we only want to retrieve those who already have a relationship (with the existing category); from these records we can retrieve the object_id, which is precisely what we need to create the new relationship with the additional category.

Removing categories

Let's now see the queries that will allow us to remove the relationships between a given category - once we know its category ID - and one or more posts using the same criteria shown above.

To remove the relationships between a category and all the posts:

To remove the relationships between a category and all the post having an active relationship with another category:

Last but not least, to remove the relationships between a category and all the post NOT having an active relationship with another category:

It's worth noting that the above queries contain an additional - and apparently redundant - subquery (SELECT * FROM wp_term_relationships instead of just wp_term_relationships) because of a known limitation of MySQL: to know more about this, read this StackOverflow answer for details.

Conclusions

That's it for the time being: we definitely hope that this tutorial will help those who are looking for a convenient way to perform some bulk tasks with the posts and/or categories of their WordPress site!

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

7 Comments on “WordPress: SQL query to add or remove categories on multiple posts A set of useful MySQL queries to perform bulk operations against the WordPress Database such as adding and/or removing categories on multiple posts

  1. Really useful script – saved me a lot of headaches. One thing to mention is that when I first tried this my posts were not being updated. When I looked into this deeper I found that some of my category ID that are displayed in the WP backend (Term_ID) are not the same as the Term_Taxonomy_ID. Once I got the right ID’s it worked perfectly.

    One thing it doesn’t do is to update the counts on the category pages ‘wp-admin/edit-tags.php?taxonomy=category’ Not critical but useful to know if there was a way to recount these.

  2. i love the query where you add a category where it has another existing category, very useful.

    but its not very straight forward how to undo this query, like remove a category where a post has an existing category.

    simply changing “INSERT INTO” to “DELETE INTO” does not work.

    i tried something like this too but doesn’t work

    DELETE FROM wp_term_relationships
    WHERE object_id, 12, 0 FROM wp_term_relationships tr2
    WHERE tr2.term_taxonomy_id = 2393 AND object_id NOT IN
    (SELECT object_id FROM wp_term_relationships tr3
    WHERE tr3.term_taxonomy_id = 12);

    any ideas?

    1. Hello there,

      I’ve just added the DELETE queries to the post: tell me if they can help you to achieve your desired result: your scenario should be the 2nd one out of the three I’ve added.

      IMPORTANT: be sure to a perform full backup of your DB before attempting the query.

      1. Hello Ryan, thanks for the super fast reply, i am still getting error (and i am doing this on a test database just to try to learn how this stuff works so thanks!)

        Running query:
        DELETE FROM wp_term_relationships
        WHERE term_taxonomy_id = 12
        AND object_id IN
        (SELECT object_id FROM wp_term_relationships tr2
        WHERE tr2.term_taxonomy_id = 2393);

        Result: /* SQL Error (1093): You can’t specify target table ‘wp_term_relationships’ for update in FROM clause */

        Is this an issue with my sql program? I use HeidiSQL

        1. Hi kje, you’re right: I’ve updated the post with a fix.

          Here’s that same fix applied for your query:

          DELETE FROM wp_term_relationships
          WHERE term_taxonomy_id = 12
          AND object_id IN
          (SELECT object_id FROM (SELECT * FROM wp_term_relationships) AS tr2
          WHERE tr2.term_taxonomy_id = 2393);

          Try it and tell me if it works now.

          To better understand why you need to do this, read here:
          https://stackoverflow.com/a/14302701/1233379

  3. Thanks. There is a plugin that adds a “Remove from category” option to the bulk post editor, however when trying to edit more than 50 posts my connection to the host times out. I knew there had to be a command to handle this!

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.

This site uses Akismet to reduce spam. Learn how your comment data is processed.