How to Properly Delete All WooCommerce Products

There may come a time when you may need to remove all the products in your WooCommerce powered ecommerce website. There’s just no easy way to do this natively in WooCommerce. I needed to do it because I changed the my product data XML file I was using to import into the site. It’s pretty straight forward, but to do it properly, you have to make sure you’re deleting all the additional data such as taxonomy, relationships, etc connected with all the existing products.

I tried using the WooCommerce Remove All Products plugin to do this, but first it didn’t work with my WordPress 4.1.1 installation and second it doesn’t remove any taxonomy.

So, to remove all your products you’ll need access to your PhpMyAdmin or some other tool like Sequel Pro to edit your WordPress database. I’ll be using PhpMyAdmin because its the easiest.

Step 1: Login to Your PhpMyAdmin

If you have more than one database, make sure you click on the correct database. If you’re not sure because you have more than one WordPress installation on the same domain, open a table and look for “wp_options”, you’ll be able to see the URL of the website you’re making changes to.

Step 2: Run the SQL Statement

If you are on the correct database, click on the SQL tab on the top right. It might be empty or have an SQL statement there from a previous command.

Screen Shot 2015-03-04 at 10.05.53 AM

With it empty, copy and paste the following code:

So the SQL text area looks like this:

Screen Shot 2015-03-04 at 9.53.36 AM

Press the “Go” button to execute the SQL statement.

Step 3: Verify All Products are Removed

Screen Shot 2015-03-04 at 10.10.02 AM

 

Tags

54 comments

  • Thanks!! this saved me a lot of time

  • How could I delete al products images in the “same” way?

    Thanks ๐Ÿ™‚

    • you can’t really do it that way. just go into Cpanel file manager or your ftp client of preference and select the ones you want to delete

  • Sรฉrgio Ventura /

    Thanks a lot. It worked for me!

  • Thanks for this post. Do you know how to bulk delete categories using SQL queries too? I’m struggling to find an answer anywhere.

  • Wow, thank you! I had done my research and found plugins, and partial “how to” articles. But I’m a newb when it comes to working in PhPAdmin. Well explained and pictured for a novice to use.

  • thank you so much….it was easy with those steps…9500 products deleted with ones ๐Ÿ™‚
    Some it was asked before, can I delete all pictures of products the same way?
    best
    Carsten

  • Ah, awesome ๐Ÿ™‚ Deleted 30.000 products in a flash!

  • How to delete the all media associated to the product?

  • Thank you for this code, looks like it’s been working for people.

    I got this error message below, if there’s a quick way to fix this I would appreciate a reply, otherwise I’ll look for other solutions to delete all products:

    Error
    SQL query: Documentation

    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’);
    MySQL said: Documentation

    #1146 – Table ‘efd.wp_term_relationships’ doesn’t exist

  • Thank you, very, very, very helpful. Worked the 1st time perfectly.

  • Thank you very much! This worked as expected and saved me a lot of time.

  • Thanks man, saved me time!

  • WOW thanks for this code. It messed up my website. THANKS A LOT!

    • Hi Ats, that is obviously something nobody wants to hear. I’ve used it more than once and others have had success with it. Maybe there’s something you need to figure out before you copy and paste code? If you’re unsure about anything, you can always ask. Its not fair to pass blame to me or others online who take the time to share their knowledge to help others; nobody is going to accept your blame.

      • Hi King and Ats,
        I use this snipped since march on many website, sql database…and never had any problem.
        And yes I have always a backup of website ready before any changes in database.
        thx ones again King
        Carsten

    • That is hugely unfair! If you are inexperienced enough to not know to take a backup of your site and database before attempting to run SQL queries that you do not understand then you are not in a position to criticise the code of another developer. Especially when several others have already used it and have come back and commented that it works.

      Either ask a reasonable question like “I got xxxxx error, what has gone wrong?”, make a constructive critique as to how to improve the OP’s code or don’t bother posting at all.

      • King Rosales /

        Thanks Adrian! I appreciate it bro. Yeah, it happens unfortunately, but hopefully he’ll learn and graduate from not blaming others ๐Ÿ™‚

  • Brilliant! This worked perfect, best solution I’ve found – 30,000+ products – Bah-bye!!! Gone ๐Ÿ™‚

    Thanks for sharing this.

  • Thank you so much!!! ๐Ÿ™‚

  • This clears out the products database. However, in the Woocommerce “Stock” tab options, the products are still appearing. When I use the “Actions” button (the pencil to edit the product), I get this response “You attempted to edit an item that doesnโ€™t exist. Perhaps it was deleted?”.
    I’m looking to find what needs to be done to clear this area too.

  • Dominic Tschan /

    Thanks a lot for that code, saved me some hours!
    Attributes are not deleted. Could you tell me what I need to add in the code so that attributes are deleted too?

    Thanks
    Best regards
    -Dominic

    • King Rosales /

      Hi Dominic, Yes I’ll be adding to this tutorial shortly. Sorry I cant do it right away, but I’ll do it as soon as I get the chance! thanks for your comment!

  • problem solved! can you delete my comment please ? thank you!

  • Hi! I have 11900 products and 5400 are with no category. I want to delete just the products with no category…
    Thank you

  • Didn’t work for me. MySQL said :
    Static analysis:

    11 errors were found during analysis.

    Unexpected token. (near “relations” at position 7)
    Unexpected token. (near “.” at position 16)
    Unexpected token. (near “*” at position 17)
    Unexpected token. (near “,” at position 18)
    Unexpected token. (near “taxes” at position 20)
    Unexpected token. (near “.” at position 25)
    Unexpected token. (near “*” at position 26)
    Unexpected token. (near “,” at position 27)
    Unexpected token. (near “terms” at position 29)
    Unexpected token. (near “.” at position 34)
    Unexpected token. (near “*” at position 35)

    SQL query: Documentation

    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’)

    MySQL said: Documentation
    #1146 – Table ‘storedatabase923782.wp_term_relationships’ doesn’t exist

  • Thanks! This is a great tip. I used the script to delete all products from a specific vendor. SKUs of this vendor start with and “R”. Here is how I amended your script:

    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 post_id FROM wp_postmeta WHERE meta_key = “_sku” AND meta_value LIKE “R%”);

    DELETE FROM wp_posts WHERE id IN (SELECT post_id FROM wp_postmeta WHERE meta_key = “_sku” AND meta_value LIKE “R%”);

    DELETE FROM wp_postmeta WHERE post_id IN (
    SELECT *
    FROM (
    SELECT post_id
    FROM wp_postmeta
    WHERE meta_key = “_sku” AND meta_value LIKE “R%”
    ) as c
    );

    Might be useful for other who are looking vor a possibility to only delete specific product.

  • Odette Judd /

    Looks like this is working well for most people…!!
    One query, are the product categories retained?

  • Mind show unexpected word “INNER JOIN” and does not delete the products

  • Most definetly works a real treat thanks King!!!

  • Hi

    I deleted all the products off my site using this code, Now WooCommerce does not redirect to the website using my affiliiate links, but instead puts the product into the cart, what do i need to do to start redirecting the customer to the correct landing page,

    Thanks in advance for any help

  • hi
    thanks king
    even the subject is old
    i want ask
    this allow you to delete all products
    but all their images in multiple sizes still available in media liberality
    is there any way to delete all images by bulk?
    thanks in advance
    http://www.goo.moda

  • How would I add an EXCEPT clause to not delete items in wp_posts where post_type is attachment? because as I run your above MySql statement in phpmyadmin it also deletes ALL brand and MFg content fromt he brands plugin which I wish to retain and have it assign the brands to the product(s) when I re-import instead of rebuilding all 300 manufacturers and brands? An EXCEPT clause seems it would be the ticket but Im not sure how to structure it so ALL other product data is removed leaving the attachment post_type data alone

    Any help with this would be hugely appreciated

  • I also remove all product’s attachments and its meta data ๐Ÿ™‚

    delete from wp_postmeta where post_id in (select ID from wp_posts where post_parent in (select ID from wp_posts where post_type = ‘product’));

    delete * from wp_posts where post_parent in (select ID from wp_posts where post_type = ‘product’);

  • Jeramiah Young /

    I figured this would be the only way to really effectively get rid of all products. Thank you so much for this article. I am, however, running into a slight error…

    Unexpected keyword. (near INNER JOIN)
    Unrecognized statement type. (near INNER JOIN)

    Here’s my code:
    DELETE relations.*, taxes.*, terms.*
    FROM development_term_relationships AS relations
    INNER JOIN development_term_taxonomy AS taxes
    ON relations.term_taxonomy_id=taxes.term_taxonomy_id
    INNER JOIN development_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’;

    has anyone else experienced anything like that?

  • Thank you SO MUCH! I was able to delete 38k products in just minutes. Only difference was my naming sequence; wp_pngbgg_options, etc. I just added the pngbgg_ to each path and have saved so much time.

  • Thank you! It worked great for what I needed.

  • Just wanted to you know I think you’re awesome. THANK YOU!!

  • Nilesh More /

    how to delete all product from specific category?

  • Hi
    How to remove products i removed from the admin but I still see them in the csv file (DB)
    My question is how to delete some items that are still in the database and images too.
    ?
    Thanks

  • Hey, thanks so much for this code, I’ve noticed that it’s missing one thing. When I created a variable product this script only deleted the product and not its variations. This results in a duplicate sku error if you try to recreate the products again. To delete all products (grouped, simple and variable) the last couple of lines should be changed like so :

    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’);

    Namely, all instances of post_type=’product’ should be changed to post_type IN(‘product’,’product_variation’)

    • Addition : You may also need to include this line :
      DELETE FROM wp_options WHERE option_name LIKE “%wc_product_children%”

  • Thank you! It helped me!

  • One question cause I haven’t tried the code yet. Will product s I’d s reset?

    If not, is this doable?

    Thanks in advance

  • WOW King, I want to EMBRACE YOU!! 54,000 products – messing with it for 3 days – and I informed the client saying I need 5 days – saw your code – BOOM – all gone in 10 mins !! THANKS MANNNN!!

  • Thank’s.It’s working perfect

Leave a Reply

Your email address will not be published.

top