How To Bulk Delete Product Variations in WooCommerce

June 3rd, 2014 - Posted by Steve Marks to MySQL, PHP, Software, Web Development.

When building any kind of E-Commerce site in WordPress I always have, and will for the foreseeable future, use the fantastic WooCommerce plugin. I’ve blogged about WooCommerce a few times in the past as I’ve come across various special requirements. Today is no different as I delve into an issue I faced when building an E-Commerce site recently.

The site in question had not thousands of products, but thousands of product variations. In summary it was for a printing company that printed posters. These posters were available in lots of sizes, qualities and quantities which resulted in one product but the number of variations running into the thousands.

Maintaining all of these variations was easy enough, especially as they didn’t get updated that often. I even purchased the WooCommerce Import Suite add on to make the process even easier.

The Problem

When I say that these variations didn’t get updated that often, they didn’t, apart from today when they wanted to revise their whole stock (!). The changes were so vast I came to conclusion that it would be easier to clear down what was there already and re-import the variations from scratch.

I could have just gone into the product in WooCommerce, clicked the ‘Variations‘ tab and deleted them all there. Two issues were presented here however:

1) There were thousands of variations. To click them all and manually delete each one would take a while.

and

2) Because there were so many variations, the ‘Edit Product‘ page was pretty much unusable due to it’s size. As a result I had removed the ‘Variations‘ tab to make working on the product possible.

I had to find another solution to remove all of the product variations in bulk.

The Solution

After thinking about the problem for a while I came up with two possible solutions which would both work:

1. Run a query on the database

I took a look into how WooCommerce stored the product variations in the database and came up with the following MySQL query which can be ran direct on the database:

UPDATE wp_posts 
SET post_status = 'trash' 
WHERE 
    post_type = 'product_variation' AND 
    post_status = 'publish'

If you only want to delete variations for a particular product then simply add a post_parent clause like so:

UPDATE wp_posts 
SET post_status = 'trash' 
WHERE 
    post_type = 'product_variation' AND 
    post_status = 'publish' AND 
    post_parent = 'X' /* WHERE 'X' is the product ID */

Note: If you’re not sure, take a backup of the database prior to running the queries above.

2. Use the Import Suite

As mentioned above, I had purchased the WooCommerce Import Suite to edit the product variations in bulk by exporting then importing a CSV.

Within the exported CSV is a column called ‘post_status‘ which dictates the status. By default this is set to ‘publish‘ but by changing this to ‘trash’ for each variation in the CSV and re-importing it, this would have the desired effect of removing all of the product variations.

I’m sure the above could be turned into a plugin at some point. As this was such a rare scenario for me however, I’m happy to do either of the above for now.

This entry was posted on Tuesday, June 3rd, 2014 at 8:28 pm by +Steve Marks and is filed under MySQL, PHP, Software, Web Development. You can follow any responses to this entry through the RSS 2.0 feed.
Comments...

Fear not, we won't publish this

Comments (7)
  1. Lyse says:

    Thank you this has saved me!

  2. Glad we were able to help :)

  3. Christi says:

    Thank you, thank you, thank you! This just saved my sanity. Adding this to my important WooCommerce notes :)

  4. Dan says:

    Youll be glad to know they have a remove all variations button now :)

  5. Steven says:

    Thank you! Worked great with woocommerce 2.x

    Big time saver.

    – Steven

  6. @Arturas – I was actually naughty and modified the plugin direct. I couldn’t find a way to do it via hooks like I normally would. For your reference the file I modified was /wp-content/plugins/woocommerce/admin/post-types/writepanels/writepanel-product-type-variable.php. I’d be interested to know if you find a better way to do this.

  7. Arturas says:

    Hi,
    Thanks for your insight.
    I am too creating website for a printer and I confronted the same problem with too many variations. Which makes product editing page in WP admin almost unusable. You said you removed variations tab to solve this problem. Could you please elaborate how you did that.
    Thanks.