Ecommerce Shopify WordPress Discussion

Change WordPress post category for multiple posts with SQL

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.
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.

February 26, 2024

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.

February 26, 2024

TurboCommerce make the better internet purchasing globaly

Turbo Multi-language Translator

Make the better internet purchasing globaly

Turbosify SEO Speed Booster

5.0 (7) Free plan available
Get better conversions by improving store loading speed Installed

Turbo Multi-language Chat - AI Customer service in one hand

TurboCommerce make the better internet purchasing globaly
Our products

The help you need, when you need it

App by Turbo Engine

3 apps • 5.0 average rating

Turbosify Speed Booster

5.0 (7)
Get better conversions by optimizing shopify store Google page speed Installed

Turbosify Translator for Wordpress Woocommerce

5.0 (74) Free Wordpress Woocommerce Plugin
Translate your wordpress website to multiple language within 1 click, no configuration needed, no No technical required

Grow your business here

Whether you want to sell products down the street or around the world, we have all the tools you need.