How To Bulk Delete Product Variations in WooCommerce
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.
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.
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.
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.