WordPress / Web Development Tutorials
(Best WordPress Tutorials)

CSSHTMLJavaScriptjQueryMySQLPHPSilvaTechnologiesWooCommerceWordpress
Silva Web Designs - Blog

How to Instantly Delete All Your WooCommerce Products With MySQL

They may come a time when you need to remove all the products from your WooCommerce store, in fact, we had to do just that recently! There isn’t an easy way to do this natively in WooCommerce and if you have a lot of products, removing them in steps can be very time-consuming. When deleting products, you need to ensure you’re deleting all the additional data related to the product such as the taxonomy, relationships and so on. Basically, anything that is connecting to the products.

So to remove all your products safely and cleanly, you will need access your phpMyAdmin so that you can run a script on your database.

1. Login to Your phpMyAdmin

Let’s login to phpMyAdmin, before proceeding, make sure you are going to run the script on the correct database. As always, take a backup prior to running the script. To ensure you have the correct database loaded; you can always look for the table wp_options and ensure it has the correct URL for your website.

2. Run the SQL Statement

Ready to remove all your products in an instance? Then now you can click on the SQL tab and you should end up on a page like this:

Now simply paste the following code into the MySQL query to delete all products. NOTE: Ensure you take a backup of the database before proceeding, just in case.


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';

Press the ‘Go’ button to execute the above SQL statement.

Another note, if your database prefix is not the default wp_, you will need to replace this with the database prefix you are using.

3. Ensure All Products are Removed

If it all worked out, if you navigate to the products page, you should now end up with an empty Products list. Awesome right?

If you are removing products because you ran an importer than didn’t quite work out, you will now find that you may have duplicate media products if you were to run a fresh import. To remove the duplicated images, have a look at this post and it will solve your issue: How to Clean up Your WordPress Media Library.

As always, we hope you found this tutorial useful if you’ve used it for a project or was not able to run it successfully, let us know in the comments below!

 

Nathan da Silva - Profile

Posted by: Nathan da Silva

Nathan is the Founder of Silva Web Designs. He is passionate about web development, website design and basically anything digital-related. His main expertise is with WordPress and various other CMS frameworks. If you need responsive design, SEO, speed optimisation or anything else in the world of digital, you can contact Silva Web Designs here; [email protected]

It’s good to share

4 thoughts on “How to Instantly Delete All Your WooCommerce Products With MySQL

  1. Need to be careful with, as it also removed the products’ categories.
    Better to use:
    — Remove all attributes from WooCommerce

    
    DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE taxonomy LIKE 'pa_%');
    DELETE FROM wp_term_taxonomy WHERE taxonomy LIKE 'pa_%';
    DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);
    

    — Delete all WooCommerce products

    
    DELETE FROM wp_term_relationships WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type IN ('product','product_variation'));
    DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type IN ('product','product_variation'));
    DELETE FROM wp_posts WHERE post_type IN ('product','product_variation');
    

    — Delete orphaned postmeta

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

    Taken from: https://gist.github.com/mikaelz/d574457cb22f1f79f337

    1. Thank you for sharing this, I guess we assumed that if you would want to remove all products, you would want to remove the product categories as well. But this is valuable data and I will definitely update this post.

  2. Hi,

    How can I delete all products that dont have stock and dont bellowing to one order. Also dont delete product categories.

Join the discussion