How to delete WooCommerce products using SKU list!

I help run an online eCommerce webshop as part of my day job – Mags Direct, and because we have thousands of magazines coming and going through the shop, we often want to trash the out of stock products, as they never come back unlike most other shops.

We normally delete them manually – or send to the trash as WordPress calls it. But a huge list of over a 1,000 products out of stock had built up, and I was asked if I could help delete them. We also were getting a 500 error when deleting some of the products, something I traced in the PHP error log to be a problem when running a pre-order plugin. I’d need to turn the plugin off before deleting the problem items, and that really meant shutting the store down during that time.

Instead, I searched the internet for solutions to deleting bulk products from WooCommerce. There were plenty of options to delete ALL products, but not what I wanted, a list of specific products. I saw people using csv upload plugins to replace all existing products, and those not included in the list delete – but that’s a rubbish way of doing it in my book – upload what I want to keep??

I could not find a single solution which just deleted the products and associated meta data for a specific list of SKUs. So I’ve written on. Hopefully Google will index this and if you need this functionality you can use it yourself, you’re welcome!

How to delete WooComerce products by SKU

There’s a few steps – do them in the same order as below – it’s all SQL – so you should be used to using PhpMyAdmin or the thick client MySQL client to connect to your database. I highly suggest you test this on a test database first – a staging server if you have one. I cannot take any liability if you lose live data – but I can tell you I ran this against our live server and did not lose products which were not in our SKU list.

The backend SQL

NOTE: In the SQL below, search and replace the WP_ table prefix with whatever you use if it’s different (often is for good security practice).

1. First create a table with one column – which is a varchar big enough for all characters of SKUs
2. Then import a CSV file with the SKUs in, one per line.

CREATE TABLE WP_SKUS_TO_DELETE ( SKU VARCHAR(20) NOT NULL , NAME VARCHAR(200) NOT NULL , PRIMARY KEY (SKU)) ENGINE = InnoDB;

Then select the table, and choose ‘import’ function in PHPMyAdmin – select your csv file which should be pre-populated with SKU’s one per line.

What we really need now is the post_id values for those SKUs – as that’s actually used in the tables we need to delete from. So lets create a 2nd table converting those SKU’s to post/object ids.

create table MD_POST_IDS_TO_DELETE
as
select post_id FROM md_postmeta WHERE post_id IN (SELECT ID FROM md_posts WHERE post_type = 'product')
and meta_key = '_SKU' and meta_value in ( select sku from MD_SKUS_TO_DELETE ) ;

If you browse that table, you should have hopefully one row for every SKU you loaded. If not, check those missing SKU’s by searching in the WooCommerce product list, you can search by SKU as well as product name.

Then it’s time to delete some data! The post table is the most important – there’s one post for each product. There’s also a post_meta table which should be cleaned up too – it has MANY rows per product in it, as it’s used to store all the extra product attributes and settings.

- delete products (saved as posts)
delete from md_posts where id in ( select post_id from MD_POST_IDS_TO_DELETE);

-- clean up associalted meta data (far more rows than products = lots of meta data per product). takes some time!

DELETE FROM md_postmeta WHERE post_id IN (select post_id from MD_POST_IDS_TO_DELETE);

NOTE: the delete from md_postmeta was very slow for me as I had a lot of rows to delete.

Deleting WordPress orphan metadata

The 2nd clean up above could be also acheived by the following sql code. I’d recommend running it as a select count(*) first to check how many rows it will delete – maybe compare that to total rows in the post_meta table – if all you’re going to delete everything, possibly bad news..

DELETE pm FROM wp_postmeta pm 
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id 
WHERE wp.ID IS NULL

It’s doing a left outer join, so will find all wp_postmeta rows, then fill in where it also finds a wp_post record for the same post id. If there’s no post – the metadata gets deleted. It shouldn’t harm anything and actually should clear out any orphan records in the meta table.