Change WordPress post category for multiple posts with SQL

Question

I have a WordPress site with hundreds of posts that have the category 'Uncategorized'. What I need to do is take these 'Uncategorized' posts and assign them to a category by the year they were created. That is, if a post was created in 2010 it should be assigned to category '2010'.

This is a bit beyond my SQL experience. This code will return a list of all the posts dated 2010.

select p.id, p.post_title from wp_posts p
inner join wp_term_relationships tr on tr.object_id = p.ID
inner join wp_terms t on tr.term_taxonomy_id = t.term_id
inner join wp_term_taxonomy tt on tt.term_taxonomy_id = t.term_id
where post_status ='publish'
and tt.taxonomy = 'category'AND
p.post_date like '2010%' 

Where my brain is falling down on the job is figuring out how to then set the post category. What I can see is that the:

• id field in wp_posts is the object_id field in wp_term_relationships
• the object_id field in wp_term_relationships gives us the term_taxonomy_id field
• and the term_taxonomy_id is the term_id in 'wp_terms'
• 'wp_terms' is where the actual category names are stored.

wp_terms table is pretty simple:

[term_id] [name] [slug] [term_group]
[3]     [2010]  [2010]  [0]

How should I go about doing this? It seems that if I know the term_id for 2010 posts should be '3', and if I knew which object_id are 2010 posts, then what I'd do is go into the wp_term_relationships table, and for those object_id's set term_taxonomy_id = '3'.

Right? If so... any hints on writing the SQL for that? (am I overthinking this?)

I could mouse through hundreds of posts and set it by hand, but that seems like the dumb way to do it.

Answer 1

UPDATE wp_term_relationships
SET term_taxonomy_id = (SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE term_id = {term_id})
WHERE object_id IN (
    SELECT p.ID FROM wp_posts p
    WHERE p.post_date LIKE '2010%' AND p.post_status = 'publish'
)
AND term_taxonomy_id = (
    SELECT term_taxonomy_id FROM wp_term_taxonomy
    WHERE term_id = (SELECT term_id FROM wp_terms WHERE name = 'Uncategorized')
);

Assumptions & Notes:

• Replace the {term_id} with the appropriate term_id for each year.
• Assumes the name of the old category is Uncategorized.
• You'll need to run a similar query for each year, replacing '2010' and {term_id} with the respective year and term_id.

If possible, please test on a sample set of data or staging environment before executing on live data.

Answer 2

And it works! Thanks @ghowkay!

For those following along at home, I'm manipulating the wordpress database with phpmyadmin, though this could all be done on the command line.

The reason this is a bit complicated is because the posts themselves are in one table, each one identified by 'id'. The categories themselves are stored in a table called 'wp_terms', each identified by 'term_id'.

The 'wp_term_relationships' table links the post to the category, which as we've seen are in two separate tables (above).

In this case we are updating the post category based on the post's date. We find the posts by date here:

WHERE p.post_date LIKE '2011%' AND p.post_status = 'publish'

In order to change posts from the year 2012 I change '2011%' to '2012%'. You can get more specific on dates by changing wildcards around the date format which is this: '2012-02-19 02:41:35'. Thus, if I wanted posts from February 2021, I'd enter: '2012-02%'.

For the other piece of this magic spell we need to look in the 'wp_terms' table and find the 'term_id' for the category we want to assign to our posts. In my test wordpress install, the category named '2012' has the 'term_id' of '5'.

Plug those two changes in, and the following code updates all posts created in 2012 to category '2012'.

UPDATE wp_term_relationships
SET term_taxonomy_id = (SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE term_id = '5')
WHERE object_id IN (
    SELECT p.ID FROM wp_posts p
    WHERE p.post_date LIKE '2012%' AND p.post_status = 'publish'
)
AND term_taxonomy_id = (
    SELECT term_taxonomy_id FROM wp_term_taxonomy
    WHERE term_id = (SELECT term_id FROM wp_terms WHERE name = 'Uncategorized')
);

As always, please please please make a database backup before executing any code like this, and I suggest testing on a copy of the site, not your live site.

I'm doing this in WordPress 6.4.2.