Site icon 峰哥分享

Bulk Remove WordPress ‘Uncategorized’ Category from posts using phpMyAdmin

fennng

We can bulk apply categories to posts from wordpress portal as shown below.


Unfortunately, we cannot bulk remove a category from posts. In my case, my default category is ‘Uncategorized’. Once I bulk assign category to my posts, I would like to remove these posts from ‘Uncategorized’. This cannot be done through wordpress portal easily, although there is a workaround.
 
Today, I will introduce a quick way to do this.

[v_error]Warning: Before following this article, make sure you have backup your database and get ready to rollback.[/v_error]

First thing that we want to do is finding out the term_taxonomy_id of ‘Uncategorized’. In my case,’Uncategorized’ has a term_id of 1.

Then we can find out that the term_taxonomy_id of ‘Uncategorized’ is 1 as well.

Now, let’s leave term_taxonomy_id along, we will need it later. The second thing we need to do is finding out the posts that contains at least 2 categories. Because we don’t want to touch the posts that only has ‘Uncategorized’ category.
 
Start a phpMyAdmin service. In my case, I use docker to create a phpMyAdmin server. You will need to know your mysql server login info. And also make sure your public IP is in your mysql server’s white list.
 
docker run –name myadmin2 –rm -e PMA_HOST=yourSqlServer -p 8083:80 phpmyadmin/phpmyadmin

 

docker run --name myadmin --rm --link wp-mysql:db  --network wp-net  --network-alias myadmin-host  -p 8083:80 phpmyadmin/phpmyadmin

Then login to phpMyAdmin and select the wordpress database. In my case, the PhpMyAdmin address is http://localhost:8083
Execute the following sql code to find out the post IDs that contains more than 1 categories.

 

-- Show WP posts ID that contains multipal categories
select object_id, count(*) as numberOfCategories
from wp_term_relationships a
join wp_term_taxonomy b
on a.term_taxonomy_id = b.term_taxonomy_id
where b.taxonomy = 'category'
group by a.object_id
having numberOfCategories > 1

 

Once executed, you will see all the object_id that contains more than 1 categories.
 
 

If you want to see the posts detail, you can use the following sql query.

-- Show WP posts that contains multipal categories
select * from wp_posts c
join 
(select object_id, count(*) as numberOfCategories
from wp_term_relationships a
join wp_term_taxonomy b
on a.term_taxonomy_id = b.term_taxonomy_id
where b.taxonomy = 'category'
group by a.object_id
having numberOfCategories > 1) Ids
on c.ID = Ids.object_id

Check the output and make sure these are the posts that you want to remove ‘Uncategorized’ from. Note that other posts that do not contain  ‘Uncategorized’  will be displayed as well as long as they have more than 1 categories. Don’t worry, we will not touch them.

If everything look all right, we can now start our deletion. But wait, let’s do one more check. Execute the following sql query to show all the entries that will be deleted from wp_term_relationships table.  Note that the last line And t1.term_taxonomy_id = 1 is important, don’t miss it. And the number 1 here is the one we found in our first step.

select * 
FROM wp_term_relationships t1
JOIN 
    (select object_id, count(*) as numberOfCategories
    from wp_term_relationships a
    join wp_term_taxonomy b
    on a.term_taxonomy_id = b.term_taxonomy_id
    where b.taxonomy = 'category'
    group by a.object_id
    having numberOfCategories > 1) Ids
ON t1.object_id = Ids.object_id
And t1.term_taxonomy_id = 1

Looks good to you? Now it’s the core operation, everything we execute before is select query that does not change the database.  They are just for you to check if everything is OK. You don’t even need to execute them. Now come to the real cool thing.
This operation will perform the deletion. You cannot undo this. Let’s change select * to delete t1 like the following:

delete t1 
FROM wp_term_relationships t1
JOIN 
    (select object_id, count(*) as numberOfCategories
    from wp_term_relationships a
    join wp_term_taxonomy b
    on a.term_taxonomy_id = b.term_taxonomy_id
    where b.taxonomy = 'category'
    group by a.object_id
    having numberOfCategories > 1) Ids
ON t1.object_id = Ids.object_id
And t1.term_taxonomy_id = 1

Click the go button to execute the query, you will get some thing like this:

Done!
Check your wordpress portable to see if everything is as expected. If not, rollback your DB.

Exit mobile version