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.
Hi – Could you explain how to view previously trashed woocommerce orders via the wordpress admin (or any other way)?
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
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!
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:
-J
Ah … that’s hilarious. I was searching all sorts of solutions to my missing trash, and never thought to check that! Much appreciated!
This was really helpful, thanks for sharing the code snippet.
You’re welcome. Glad it was useful.
Absolutely genius. Been staring at duplicate EVERYTHING for so long I couldn’t think straight. Trash ’em all.
Why don’t you just go ahead and delete the postmeta and posts altogether instead of putting them into “trash” status?
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.
How delete Pending orders only?
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 🙂
brilliant, thank you very much for this.. i’m wrangling similar amounts of data…
You welcome, Tim.
Thanks for sharing … after cloning a site I needed to delete 1000’s of old orders … it worked a treat 🙂
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!
Hi Scott, sorry for the late reply. That’s a fairly complicated SQL query as orders span a number of tables. Give this SO post a read. It should get you going down the right path.
https://stackoverflow.com/questions/24904413/how-to-delete-completed-orders-in-woocommerce-using-a-my-sql-query
Let me know how it goes!
-Jibran
Can anyone please tell me where to insert the code and execute it?
Hi Shane, you can run it in phpmyadmin. Check with your host on how to connect to it. You can read more here:
https://codex.wordpress.org/phpMyAdmin
-Jibran
Thanks Jibran. I do have access to the phpmyadmin and some familiarity with it, I’ve set up the test site on a sub-domain with a test sql database, but where in phpmyadmin do you execute the code you’ve provided? If you could point me in the right direction it’d be much appreciated thanks.
More accurately it’s probably in the mysql command window, right? lol
Yup you got it. I did a quick google search and found this if it helps.
https://www.siteground.com/tutorials/phpmyadmin/phpmyadmin_mysql_query.htm
That saved me a tonne of time, thanks! Apologies for the brain fart earlier. I love the internet and clever coders like you 🙂
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’
AWSOME! Thanks Jibran.
Can you tell me how to delete orders older than 2 years only?
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