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:

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


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

Related Posts

80 responses to “How to Properly Delete All WooCommerce Products”

  1. Kinich Avatar

    Thanks!! this saved me a lot of time

  2. Mr Fashion Avatar
    Mr Fashion

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

    Thanks :)

    1. Dan Avatar
      Dan

      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

  3. Sérgio Ventura Avatar
    Sérgio Ventura

    Thanks a lot. It worked for me!

  4. Ade Avatar
    Ade

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

  5. Max Avatar
    Max

    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.

  6. Carsten Avatar
    Carsten

    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

  7. Kevin Avatar

    Ah, awesome :) Deleted 30.000 products in a flash!

  8. Augusto Avatar
    Augusto

    How to delete the all media associated to the product?

  9. Robert Avatar
    Robert

    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

    1. Jason Avatar
      Jason

      What is the prefix for you tables in your database? change the “wp_” to whatever your prefix is then it should work.

      1. King Rosales Avatar
        King Rosales

        Thanks for the added support on this one Jason!

      2. Dele Avatar
        Dele

        i changed the prefix of my tables and it works GREAT!!!. Thanks

  10. Tom Avatar

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

  11. Norman Avatar
    Norman

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

  12. Jay Avatar
    Jay

    Thanks man, saved me time!

  13. Ats Avatar
    Ats

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

    1. King Rosales Avatar
      King Rosales

      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.

      1. Carsten Avatar
        Carsten

        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

        1. King Rosales Avatar
          King Rosales

          Thanks Carsten! I appreciate the feedback and sticking up for me.

    2. Adrian Hart Avatar

      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.

      1. King Rosales Avatar
        King Rosales

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

  14. Gerry Avatar

    Brilliant! This worked perfect, best solution I’ve found – 30,000+ products – Bah-bye!!! Gone :)

    Thanks for sharing this.

    1. King Rosales Avatar
      King Rosales

      You’re welcome bro!

  15. nova Avatar
    nova

    Thank you so much!!! :)

  16. maxtmon Avatar
    maxtmon

    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.

  17. Dominic Tschan Avatar
    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

    1. King Rosales Avatar
      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!

  18. Paul Avatar

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

  19. Dan Avatar
    Dan

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

  20. Frida Avatar
    Frida

    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

  21. Peter Avatar
    Peter

    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.

  22. Odette Judd Avatar
    Odette Judd

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

  23. Scott Hamblin Avatar

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

  24. Steven Roberts Avatar

    Most definetly works a real treat thanks King!!!

  25. gary Avatar
    gary

    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

  26. goomoda Avatar

    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

  27. Steven Roberts Avatar

    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

  28. Bruno Rodrigues Avatar
    Bruno Rodrigues

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

  29. Jeramiah Young Avatar
    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?

  30. Sandra Avatar
    Sandra

    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.

  31. Pedro Dias Avatar
    Pedro Dias

    Tks so much!

  32. Trumpet Avatar
    Trumpet

    Thank you! It worked great for what I needed.

  33. Sarah Avatar
    Sarah

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

    1. King Rosales Avatar
      King Rosales

      THANKS SARAH! You’re welcome

  34. Nilesh More Avatar
    Nilesh More

    how to delete all product from specific category?

  35. Moses Avatar
    Moses

    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

  36. Anton Avatar
    Anton

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

    1. Anton Avatar
      Anton

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

  37. Nick Avatar
    Nick

    Thank you! It helped me!

  38. Costas Avatar
    Costas

    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

  39. Fahad Hassen Avatar
    Fahad Hassen

    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!!

    1. King Rosales Avatar
      King Rosales

      hahaha awesome Fahad! yeah, definitely a life/time saver!

  40. Katrin Avatar
    Katrin

    Thank’s.It’s working perfect

    1. King Rosales Avatar
      King Rosales

      Awesome! Glad it worked for you :)

  41. Oscar Avatar
    Oscar

    Thank you, really helped.

  42. Shiva Avatar
    Shiva

    Code worked like a charm to delete products. It seems to leave SKUs and product IDs behind. Any method of deleting all the product IDs from some other related tables?

  43. Webmonkey Ol'timer Avatar
    Webmonkey Ol’timer

    Awesome! Hallelujah!

    But this is kind of old so for me I had to change wp_ to wpua_ and I have no idea why. I’m a novice old time web monkey. Trial and error guy. I gues woocommerce has evolved and changed table names et cetera.

    Worked like a charm. Here are my changes.

    – – – – –
    DELETE relations.*, taxes.*, terms.*
    FROM wpua_term_relationships_term_relationships AS relations
    INNER JOIN wpua_term_taxonomy AS taxes
    ON relations.term_taxonomy_id=taxes.term_taxonomy_id
    INNER JOIN wpua_terms AS terms
    ON taxes.term_id=terms.term_id
    WHERE object_id IN (SELECT ID FROM wpua_posts WHERE post_type=’product’);

    DELETE FROM wpua_postmeta WHERE post_id IN (SELECT ID FROM wpua_posts WHERE post_type = ‘product’);
    DELETE FROM wpua_posts WHERE post_type = ‘product’;

  44. Webmonkey Ol'timer Avatar
    Webmonkey Ol’timer

    So I pasted the wrong code. Hehe! I guess I had made a safety copy in an earlier trial. See below first 2 lines and just switch them out.

    DELETE relations.*, taxes.*, terms.*
    FROM wpua_term_relationships_term_relationships AS relations

    DELETE relations.*, taxes.*, terms.*
    FROM wpua_term_relationships AS relations

    1. King Rosales Avatar
      King Rosales

      Hey Marcus, thanks so much for the update and comment. yeah my post is a little dated but once I have some time to update the post I will. I appreciate your feedback bro! I remember the old webmonkey days hahaha, totally awesome bro!

  45. Devzons Avatar
    Devzons

    It works perfectly. Save a lot of time. Thanks

  46. SteveVDM Avatar
    SteveVDM

    Hey, I ran the query on phpmyadmin from cpanel, now my site is not loading anymore,
    just getting a “Waiting for {websites name}”, Could this SQL break anything?

  47. Vince Avatar
    Vince

    Thank you for this. It worked perfectly :D

  48. Nemanja Dizdarevic Avatar
    Nemanja Dizdarevic

    Great job. God bless you and keep you.

  49. Dean Avatar
    Dean

    amazing saved me hours of work, thanks

  50. Patrick Cunningham Avatar
    Patrick Cunningham

    Worked perfectly for me. I’ve been using a bulk delete plugin, but the product records remain in the database.

    Love to have the code for deleting my categories…

    Thanks for the code, and I’m sorry you took some crap for it. When I was about to run it, I stopped in my tracks to reassure myself I had current back ups of the site and database. I use WP Engine so I don’t have to worry as I have daily back ups of the site and db, as well as a staging environment copy of both.

    Take care and thank you for supporting low to medium skill WP developers.

    Best,

    Patrick

  51. Kajetan Avatar
    Kajetan

    That is awsome !
    I had to only replace wp_ with _ZW4_ and all gone in a seconds
    Thanks a lot mate!

    1. King Rosales Avatar
      King Rosales

      Glad to hear buddy! You’re most welcome!

  52. Codex Avatar
    Codex

    Hay King Rosales,
    How can I skip 20 products in each parent category before deleting all products?
    Thanks

  53. Dave Hoe Avatar

    This was superb. Saved an awful lot of time. Thank you for posting.

    1. King Rosales Avatar
      King Rosales

      You’re welcome! still saves me lots of time too.

  54. Justin Hebrio Avatar
    Justin Hebrio

    the code ran however products are still on my site. how do i delete all of them

  55. Shahr Avatar
    Shahr

    Thank You

  56. Shaady | EasyComp Shop Avatar
    Shaady | EasyComp Shop

    Should not take to long right?!

    1. King Rosales Avatar
      King Rosales

      With 100,000 products it will take long compared to a typical catalog of 5,000 – 10,000 SKUs. Personally I would break up the file into smaller files then import. Its an easy way to catch errors in the file, so you’re not having to wait a long time for so many SKUs.

  57. Ruwan de Silva Avatar
    Ruwan de Silva

    Thanks.

    1. King Rosales Avatar
      King Rosales

      Most welcome!

  58. ben Avatar
    ben

    I saw a version that handles Paroduct variation. Is this a newer version?

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

  59. DJ Avatar
    DJ

    How do I delete just one product ID? I keep getting 502 errors when doing on live site – want to run a query to remove it.

  60. Micha Avatar
    Micha

    Hello a really great thing.
    Is it possible to delete the properties of the articles?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.