WooCommerce: How To “Trash” All Orders Really Fast

WooCommerce Logo
Some context
I am working on a WooCommerce installation where I’m dealing with literally thousands of historical orders. All of which I am programmatically importing (including user accounts!) and doing a bunch of special updates, which are numerous and complicated, but not the subject of this post. But this gives you enough context on which to move forward.

As I develop this import tool, these thousands of orders need to be removed (or “trashed”) after each test I make (finding and fixing bugs) because naturally you can’t have duplicate orders. Am I right or what?

The WooCommerce order admin interface is robust and powerful, but not for bulk trashing. I can only send around a maximum of 350 orders to the trash at any one time. So this process of “resetting” the orders is time consuming and cumbersome.

I searched and thought and searched and thought some more, wondering how to quickly trash every single one of those damned orders after each test. And then I remembered: an order is just a custom content type. Well I didn’t forget, but if you’ve ever developed anything, you’re jugging hundreds of lines of code and switching from language to language and from one task to the next. Not everything you work on is at the forefront of your brain, our human RAM, if you will. I think you’re following me. We basically can’t see the forest through the trees.

But then I did remember.

The solution
The order custom content type (shop_order) that is WooCommerce has all the basic functionality that is WordPress. Any content type has a post_status field associated with it and can have values such as: publish, draft, trash, etc. You can read up on post statues, here.

To quickly send all orders to the trash run the following sql command. Super easy. This would work for any content type you’d just need to update the where clause.


update wp_posts set post_status = 'trash' where post_type = 'shop_order';

Then you can go one step further and view your order trash via WordPress admin and click on ‘Empty Trash’. I wouldn’t recommend using this anywhere near a production machine. Use at your own risk. 😉

Enjoi.

Posted in: Code Samples, Development  |  Tagged with: , ,  |  Leave a comment
24 comments on “WooCommerce: How To “Trash” All Orders Really Fast
  1. Andrew says:

    Hi – Could you explain how to view previously trashed woocommerce orders via the wordpress admin (or any other way)?

    • jibran says:

      Hi Andrew, in WP admin > WooCommerce > Orders, there should be what’s called a “views” menu just above the filter dropdowns. You’ll see: All (123) | Trash (10) where the numbers are the order count for the respective view. Click ‘Trash’ to view all trashed orders. -Jibran

      • Andrew says:

        Weird – for some reason I don’t have a “Trash” option here. I’ve contacted woo to see if one of their plugins (or another one) might be screening this off. Thanks for the help!

        • jibran says:

          Looks like the trash link doesn’t display until there are actually trashed orders. I just experienced the same thing so I ran a couple tests on two of my sites and sure enough if there are no trashed orders, no link displays. So, try trashing an order and see if the link displays. Let me know what happens!

          Also, If you’re comfortable running SQL commands on your database you can verify that there are no trashed orders by the following script:

          select * from wp_posts where post_type = 'shop_order' and post_status = 'trash';

          -J

          • Andrew says:

            Ah … that’s hilarious. I was searching all sorts of solutions to my missing trash, and never thought to check that! Much appreciated!

  2. kid2000 says:

    This was really helpful, thanks for sharing the code snippet.

  3. Lauren Gray says:

    Absolutely genius. Been staring at duplicate EVERYTHING for so long I couldn’t think straight. Trash ’em all.

  4. William Oetting says:

    Why don’t you just go ahead and delete the postmeta and posts altogether instead of putting them into “trash” status?

    • jibran says:

      A good thought but I felt it best to let WP handle the cleanup. Unless deleting from the trash is taking forever. Easy enough to change the query.

  5. Essam says:

    How delete Pending orders only?

  6. Essam says:

    I used this code to delete all pending payment :

    DELETE FROM bo_posts WHERE post_type = ‘shop_order’ and post_status=’wc-pending’

    Please how use it to delete pending payment from

    DELETE FROM bo_woocommerce_order_itemmeta
    DELETE FROM bo_woocommerce_order_items

    Thanks

    • @Essam:
      Use this SQL Query to move your pending orders to trash, then use woocommerce admin panel to empty trash which will clear those orders’ post meta and any other tables those orders had stored data, thereby cleaning up the database:

      update wp_posts set post_status = ‘trash’ where post_type = ‘shop_order’ and post_status = ‘wc-pending';

      You can use that query to change the post status of all orders from one status to another, example change ‘trash’ to ‘completed’ – that would change all pending orders to completed orders… Changing the orders to a status of ‘trash’ is to simplify the deletion process, by moving the orders to trash then using the woocommerce admin area to “empty trash” in the backend of your website you’ll be using woocommerce to handle the actual deletion and database cleanup :)

  7. tim prebble says:

    brilliant, thank you very much for this.. i’m wrangling similar amounts of data…

  8. Dave says:

    Thanks for sharing … after cloning a site I needed to delete 1000’s of old orders … it worked a treat :)

  9. Scott says:

    Thank you for the SQL query. Now that all the orders are in the trash, what is the SQL query to delete them permanently? If I go through the WordPress Dashboard it takes so long that my server gives a “Bad Gateway” error and my request times out. Thanks!

  10. Shane says:

    Can anyone please tell me where to insert the code and execute it?

  11. Shane says:

    That saved me a tonne of time, thanks! Apologies for the brain fart earlier. I love the internet and clever coders like you :)

Leave a Reply

Most recent work

  • Our Restroom Our Restroom is an (inter)national crowdsourced campaign who’s goal is simple: compel businesses to make their single occupancy restrooms gender neutral. Championed by Kristin Russo, designed by Allison Weiss, built by me on WordPress.
  • How To Win At Feminism The badasses over at Reductress are at it again with their new book How To Win At Feminism.
  • Reductress I crank out on-going web projects for Reductress. Super fun as you can imagine.
  • Everyone Is Gay Everyone Is Gay is an indispensable question and answer resource for the LGBTQ community. Hell, it’s great advice for anyone with a body with an emotion or two. I made the site responsive among other things. Give it a look see.

Need hosting?

  • Media Temple My go to hosting provider. You get rockstar hosting and I make a little cash. No pressure, just clean honest fun.

Past work

  • Christine Chaney Creative The online presence of Seattle artist Christine Chaney. Specializing in architecture, art and apparel.
  • Get Your Shit Together This is a site dedicated to helping you get your shit together before an unexpected tragedy, like the loss of a loved one.
  • IHG Instagram Sweepstakes An awesome Instagram driven sweepstakes site for IHG.
  • Trivial Beersuit A site that provides professional pub trivia to bars and restaurants.
  • Audi YouTube Channel This site was a quick turn-around hair pulling adventure for the Super Bowl: revamping Audi’s YouTube Channel.
  • Teach.org The new Teach.org. Providing tools to help students become teachers.
  • Valhalla DSP A one man Seattle based company, writing pretty sick professional audio plugins. Check ’em out!
  • Microsoft Visual Studio 2012 Launch This sweet new Visual Studio 2012 Launch website sports a mobile first, responsive web design architecture and comes in 14 different languages.
  • Nice Manners Music Nice Manners is an LA based recording studio offering their clients all sorts of digital audio services. This is a demo of an internal genre based music search & player I built for them. I am ecstatic about launching this project—check it out.
  • Holiday Inn Tumblr A quick, fully responsive update to the Holiday Inn Tumblr page.
  • Microsoft's Art of Touch An artful HTML5 Microsoft website. Sadly, this site is no longer live.
  • Microsoft Hardware Healthy Computing Tool A useful little tool to help you find the perfect Microsoft mouse or keyboard.
  • Pella Professional Pella manufactures high quality doors and windows. This is their professional site.
  • Imaging the World A non-profit dedicated to providing ultrasound services in remote areas of the world.
  • Ginny Ruffner: A not so still life A site for a documentary film about Seattle artist Ginny Ruffner.

Blogroll