Updating multiple WooCommerce variant product shipping classes at once with SQL

I came across a problem recently whereby I needed to update a few thousand WooCommerce product variations’ shipping classes. To explain, this store had hundreds of products, each with at least a dozen variations. These variations were typically split into 2 types, we’ll call them Type A and Type B.

Type A variations were absolutely fine inheriting the existing shipping class from the parent product. Type B variations however, took longer to ship, so the client needed the shipping class to change to show an increased delivery time in the cart. (P.S. We were using Table Rate Shipping to achieve this.)

This in itself is fine, however it required 3,250 product variations to get updated!

There are many plugins out there that do bulk updating of WooCommerce products/variations, but I’m always reluctant to pay for something I can do myself for free! Surely there’s a way to achieve this with SQL? And there is!

The SQL Query

UPDATE wp_term_relationships 
SET term_taxonomy_id='$1' 
WHERE (term_taxonomy_id='$2' 
AND object_id IN ($3));

What this code does is run a query in the wp_term_relationships table, setting the term_taxonomy_id to the desired ID ($1) of the new shipping class we created. It does this by checking for the old shipping class in term_taxonomy_id ($2), and only if the object_id matches an array of products/variations we wish to change ($3).

How do we find the three $variables?

I’ll break down how to find the various IDs you need to use in $1, $2, and $3.

$1

This is the term_taxonomy_id of your desired shipping class, the one we want these products to have when we’re finished. You can find this ID by searching in the wp_terms table for the slug of the shipping class you created.

SELECT * FROM wp_terms WHERE slug = "your-slug";

There should only be 1 result, your new shipping class. If it doesn’t return anything, check you’ve entered the correct slug. The term_id is the value you need for $1.

$2

This is actually very similar to finding out $1, except $2 is the shipping class you want to remove from the products. Carry out the steps above, but this time replace the slug with that of the old shipping class.

$3

This one is the easiest of them all! $3 is the product ID you wish to change. If we didn’t specify anything in the WHERE clause about which posts we wanted to affect, then we’d end up replacing the old shipping class on all the products that use it!

$3 – Single products

If you are only affecting one product, then simply look at your list of variations in WooCommerce, and note down the variation ID. Remember that when you enter the variation ID into the WHERE clause, it must be surrounded by single quotes:

UPDATE wp_term_relationships 
SET term_taxonomy_id='$1' 
WHERE (term_taxonomy_id='$2'
AND object_id IN ('12'));

$3 – Multiple products

If you’re looking to change multiple variations (like I was), then you need to include an array of variation IDs in the object_in condition, like so:

UPDATE wp_term_relationships 
SET term_taxonomy_id='$1' 
WHERE (term_taxonomy_id='$2'
AND object_id IN ('12', '13', '14'));

This can soon become an issue if you have thousands of IDs to change! It can be done, you can have thousands of IDs inside this array, but finding them all might be a challenge.

$3 – How to find lots of multiple products

In my example, I was looking to change the shipping classes of all Type B variations, which were all identified by a certain variation attribute they shared. That meant I could run a custom SQL query to find all the IDs.

Probably the easiest way to do this is a search in the wp_postmeta table. Something like this:

SELECT * FROM wp_postmeta WHERE meta_value = "my-meta-value";

All you need to do is change my-meta-value above to be the slug of the attribute you’re searching for. You should return all the rows matching that query, and assuming you want to change all of the shipping classes for these items, you just need to export this result and use the post_id column as your array.

Running the Query

Once you’re done, you should have a finished query. Mine looked a bit like this:

UPDATE wp_term_relationships 
SET term_taxonomy_id='1203' 
WHERE (term_taxonomy_id='8' 
AND object_id IN ('100', '102', '104'));

I’ve removed all the IDs used from the WHERE clause and just left the first 3 above, but you get the idea.

Because this was such a huge query, I used WP-CLI (an excellent tool if you’re not already using it) to do a custom SQL query.

I added my entire query into a file called query.sql in the root of the site, and then ran these commands…

# Backup the current db
wp db export backup.sql

# Run the query
wp db query < query.sql

# When happy, remove the backup file and the query.sql
rm db.sql query.sql

Of course, be sure to run this on a test site or local environment before doing it in production! And make sure you refresh your site’s cache once done (if your site uses a caching plugin).

If you have any questions or comments, please let me know!

Tools I used in this tutorial

• Sequel Pro (to browse your local database)
• WP-CLI
• Terminal