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.
27 Comments
Feb 28 at 5:21am
Hi – Could you explain how to view previously trashed woocommerce orders via the wordpress admin (or any other way)?
Feb 28 at 10:33am
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
Mar 03 at 12:36am
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!
Mar 03 at 6:24am
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
Mar 04 at 1:39am
Ah … that’s hilarious. I was searching all sorts of solutions to my missing trash, and never thought to check that! Much appreciated!
May 16 at 4:52am
This was really helpful, thanks for sharing the code snippet.
May 16 at 3:00pm
You’re welcome. Glad it was useful.
Jul 29 at 6:19pm
Absolutely genius. Been staring at duplicate EVERYTHING for so long I couldn’t think straight. Trash ’em all.
Aug 12 at 11:50am
Why don’t you just go ahead and delete the postmeta and posts altogether instead of putting them into “trash” status?
Aug 13 at 1:12pm
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.
Aug 13 at 12:14pm
How delete Pending orders only?
Aug 13 at 12:48pm
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
Apr 07 at 12:36pm
@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 🙂
Feb 06 at 8:11pm
brilliant, thank you very much for this.. i’m wrangling similar amounts of data…
Feb 15 at 7:19pm
You welcome, Tim.
Apr 12 at 5:36am
Thanks for sharing … after cloning a site I needed to delete 1000’s of old orders … it worked a treat 🙂
May 22 at 5:15pm
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!
Jul 17 at 9:13pm
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
Jul 17 at 8:51pm
Can anyone please tell me where to insert the code and execute it?
Jul 17 at 9:08pm
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
Jul 17 at 9:14pm
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.
Jul 17 at 9:17pm
More accurately it’s probably in the mysql command window, right? lol
Jul 17 at 9:21pm
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
Jul 17 at 9:31pm
That saved me a tonne of time, thanks! Apologies for the brain fart earlier. I love the internet and clever coders like you 🙂
Nov 17 at 1:14pm
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’
Jun 11 at 3:03am
AWSOME! Thanks Jibran.
Can you tell me how to delete orders older than 2 years only?
Oct 16 at 11:45am
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