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
27 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 🙂

  12. Svetkol says:

    If you want to delete all orders directly without going to the trash, execute the following sql command: DELETE FROM `wp_posts` WHERE `post_type` = ‘shop_order’

  13. nummell says:

    AWSOME! Thanks Jibran.

    Can you tell me how to delete orders older than 2 years only?

  14. odense3dprint says:

    there are plugins that promise to be able to reset next order number to 1

    but how do i do this manual in mysql? after deleting all test orders via phpmyadmin

    SELECT * FROM `wp_posts` WHERE `post_type` LIKE ‘shop_order’ ORDER BY `post_date` DESC

    (and then deleting them)

    i find that when i create an order they start at 101 or thereabouts and not at 1

    if anyone knows how to truely reset to 1 via direct database access let me know, and yes i know to backup etc etc

Leave a Reply to nummell Cancel reply