Have you ever needed to remove all of your WooCommerce products from your database? Have you ever had so many that deleting them from the admin would take forever? Well, do I have news for you!
My husband has an e-commerce site to sell new and used media. He has over 100,000 items in inventory. He sells on Amazon and has two physical stores. There are a lot of moving parts. His POS/inventory system doesn’t have an open API, so I have to update the inventory MANUALLY every day. I say manually, but I actually use WP All Import and a cron job. Easier but not ideal. The POS/ inventory software my husband uses is, in my opinion, pretty crappy, but he looked forever, and it’s the best that he has found thus far. So, it is what it is.
At least twice a year, I have to reset inventory. This could be for a variety of reasons, but it is often because of a bad import that royally messes things up. I’ve grown to really dislike special characters; the POS vendor doesn’t always do the best job of sanitizing the file.
The Magic (and the reason you’re reading any of this)
Obviously, it would take forever for me to bulk edit from the admin screen. I found this really helpful snippet to delete all items from the database.
DELETE relations.*, taxes.*, terms.*
FROM wp_term_relationships AS relations
INNER JOIN wp_term_taxonomy AS taxes
ON relations.term_taxonomy_id=taxes.term_taxonomy_id
INNER JOIN wp_terms AS terms
ON taxes.term_id=terms.term_id
WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type='product');
DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type = 'product');
DELETE FROM wp_posts WHERE post_type = 'product';
Whose code is this?
It’s not mine. This fantastic ditty comes from King Rosales. After the third or fourth time I used it, I thought, “Golly, I use this a lot. Maybe I should share because it’s super helpful.” So, here you go. They have a great how-to. It can be a little scary because you are doing this from your PHPAdmin. Make sure you take a backup first or try it on a staging site.