There are times when you have thousands of WooCommerce orders, products, categories, tags, coupons, customers or custom fields and the “Bulk Edit > Delete” option in the WordPress dashboard is simply not cost- and time-effective.
Maybe because your website goes down as it can’t process that much information at the same time, or maybe because you’re on a low budget and can’t afford to hit “delete” 100 or 1000 times by hand.
Thankfully, WooCommerce information is stored in the WordPress database, which means we can access it, write a “SQL DELETE statement”, and bulk delete anything you like, in seconds. Of course, and you will find warnings below, NEVER DO THIS on a live website as I can’t guarantee this won’t have any side effects.
So, have fun!
How to Access the WordPress Database
Your hosting control panel should have a “phpMyAdmin” icon or link. This will allow you to login to your database. Here’s an example with SiteGround hosting.
Now, select your database from the left and once its tables get listed in the right panel note down the database tables prefix. By default this is “wp_“, but each WordPress install could possibly use a different custom prefix. In my case it’s “apos_“, as you can see from the screenshot.
Then, go to the “SQL” tab and write one of the DELETE statements you find below, based on what you need to erase. Of course, you must change all the occurrences of “wp_” to your custom WordPress database table prefix (“apos_” in my case). That’s it!
Disclaimer: this could mess up your entire WordPress database. Use only if you know what you are doing and also test it first on staging.
1. Bulk Delete All Orders
For WooCommerce HPOS
If WooCommerce HPOS (High-Performance Order Storage) is enabled, order data can now be found inside dedicated database tables prefixed by wp_wc_ or similar, depending on your setup.
// change "wp_" to your custom WordPress database table prefix
DELETE FROM wp_wc_orders_meta;
DELETE FROM wp_wc_orders;
DELETE FROM wp_wc_order_addresses;
DELETE FROM wp_wc_order_operational_data;
DELETE FROM wp_commentmeta WHERE comment_id IN ( SELECT ID FROM wp_comments WHERE comment_type = 'order_note' );
DELETE FROM wp_comments WHERE comment_type = 'order_note';
For WooCommerce pre-HPOS
If you installed WooCommerce before version 8.2 and you didn’t migrate to HPOS, WooCommerce still stores order data in the _posts and _postmeta tables.
// change "wp_" to your custom WordPress database table prefix
DELETE FROM wp_woocommerce_order_itemmeta;
DELETE FROM wp_woocommerce_order_items;
DELETE FROM wp_commentmeta WHERE comment_id IN ( SELECT ID FROM wp_comments WHERE comment_type = 'order_note' );
DELETE FROM wp_comments WHERE comment_type = 'order_note';
DELETE FROM wp_postmeta WHERE post_id IN ( SELECT ID FROM wp_posts WHERE post_type = 'shop_order' );
DELETE FROM wp_posts WHERE post_type = 'shop_order';
2. Bulk Delete All Products
// change "wp_" to your custom WordPress database table prefix
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' );
3. Bulk Delete All Trashed Products
// change "wp_" to your custom WordPress database table prefix
DELETE FROM wp_postmeta WHERE post_id IN ( SELECT ID FROM wp_posts WHERE post_type = 'product' AND post_status = 'trash' );
DELETE FROM wp_posts WHERE post_type = 'product' AND post_status = 'trash';
4. Bulk Delete All Coupons
// change "wp_" to your custom WordPress database table prefix
DELETE FROM wp_postmeta WHERE post_id IN ( SELECT ID FROM wp_posts WHERE post_type = 'shop_coupon' );
DELETE FROM wp_posts WHERE post_type = 'shop_coupon';
5. Bulk Delete All Order Notes
// change "wp_" to your custom WordPress database table prefix
DELETE FROM wp_commentmeta WHERE comment_id IN ( SELECT comment_ID FROM wp_comments WHERE comment_type = 'order_note' );
DELETE FROM wp_comments WHERE comment_type = 'order_note';
Thanks for this!
Curious how you’d maybe rewrite #1 for HPOS to only delete orders that are in the trash.
I don’t want/need to delete ALL orders, I’ve moved a whole ton (50k+), but not all, to the trash and just want to empty the trash. Any thoughts on if this can be done? Thanks!
Good question! The wc_orders table has a “status” column you can filter orders with (status = “trash”). After that, you will also need to filter the other SQL calls, to make sure you’re only selecting the correct IDs (each sub table should have the “order_id” column). Hope this helps!
Hi Rodolfo, thank you for this super fast method! I have used UPDATE wp_posts SET post_status = ‘trash’ WHERE post_type = ‘shop_order’; to bulk move orders into the trash so I was thinking there would be another status to move shop_orders back out of the trash with UPDATE wp_posts SET post_status =’???’ WHERE post_type = ‘shop_order’ I’m nervous to move all to the trash when I don’t know how to move them back out of trash if i decided not to delete or make a mistake. Thank you!
Very good point! Whenever an order is “untrashed”, by looking at the plugin code, this runs:
So it basically restores the previous status by looking into the ‘post_status’ order post custom field. If you’re able to “translate” this PHP into SQL, you can definitely achieve what you’re looking for!
Oops!
DELETE FROM wp_commentmeta WHERE comment_id IN ( SELECT ID FROM wp_comments WHERE comment_type = ‘order_note’ );
threw an error
#1054 – Unknown column ‘ID’ in ‘field list’
Should that be comment_id?
DELETE FROM wp_commentmeta WHERE comment_id IN ( SELECT comment_id FROM wp_comments WHERE comment_type = ‘order_note’ );
Yesss sorry about that!
It would be great if this was updated for HPOS.
Good point! This is now done
Hi,
I’ve bulk deleted all orders, it said loads of rows are deleted, but I’m still seeing all orders.
How is this possible?
Cheers,
Nathan
I hope you tried that first on staging!
Honestly I don’t know – are you on HPOS maybe?
Yeah it seems like your SQL example doesn’t delete data from HPOS tables.
Gotcha, this is not HPOS compatible (as of today)
Hi Rodolfo,
Many thanks for sharing this valuable information.
With 1.Bulk Delete All Orders
DELETE FROM wp_woocommerce_order_itemmeta;
DELETE FROM wp_woocommerce_order_items;
DELETE FROM wp_comments WHERE comment_type = ‘order_note’;
DELETE FROM wp_postmeta WHERE post_id IN ( SELECT ID FROM wp_posts WHERE post_type = ‘shop_order’ );
DELETE FROM wp_posts WHERE post_type = ‘shop_order’;
I would prefer to delete all orders before a specific date 1.01.2020 only. What changes in the SQL should be done for that?
Hello Ken, thanks so much for your comment! Yes, this is definitely possible, but I’m afraid it’s custom work. If you’d like to get a quote, feel free to contact me here. Thanks a lot for your understanding!
Thank you soooooo much for sharing this method. It saved me a lot of time. I love it!
Great!
Thanks for this! Saved me a lot of time and helped make sure I wasn’t leaving orphaned records all over the place.
Cheers!
Cool!
I just deleted about 20,000 guest orders in under 5 min.
Thank you!
Fantastic!
Good evening.
Sorry to bother you. We came across your website by accident and applied your SQL commands.
Why we are writing to you today is because WP or WOOCOMMERCE Support were unable and/or unwilling to provide us with the much needed SQL command.
We just want to list and save the complete products in 4 languages including metadata, categories and subcategories and the assignment of the images per article. (We don’t want a CSV file and/or a backup)
We will then delete the SQl database and set it up from scratch.
We would appreciate if you could provide us the relevant command. If you incur costs as a result, we will be happy to pay them for you.
Awaiting a reply from you, thank you in advance.
Kind regards
Matti
Hello Matti, thanks so much for your comment! Yes, this is definitely possible, but I’m afraid it’s custom work. If you’d like to get a quote, feel free to contact me here. Thanks a lot for your understanding!
Hi
Thank you for the article. This could be our savior.
Our website is currently under attack by a low-life who is someone generating fake orders and in the last 24 hours there have been over 2000 of them!!!!! We simply cannot delete the Failed orders fast enough. Nor have we been able to stop the attack.
I guess my question is – can FAILED orders be cancelled in the manner you are suggesting and how much would you charge to do this for us?
Thank you very much
Hello Bryan – yes the SQL statement can be adjusted to only include failed orders. However, I fear you may need to secure your website first as you can’t keep deleting stuff every day… Anyhow, if you’d like to get a quote, feel free to contact me here.
Can we delete the coupons in bulk but from a specific date ?
Hi Suhail, thanks so much for your comment! Yes, this is definitely possible, but I’m afraid it’s custom work. If you’d like to get a quote, feel free to contact me here. Thanks a lot for your understanding!
Is there a way to delete all “customer” role accounts?
I’m sure some SQL can be written to achieve that, just I don’t have that right now. If you’d like to get a quote, feel free to contact me here. Thanks a lot for your understanding!
Is it possible to delete all order data in the Analyics too ?
I’m sure there is a way. I don’t use Analytics so I didn’t bother researching it
how to remove all product tag ?
Try with https://sceptermarketing.com/bulk-delete-product-tags-in-woocommerce-or-attributes/
Hi, thank you for that snipped. I found a problem that data form deleted orders exists in Woocommerce Analytics.
Not a problem of mine, I have that disabled LOL 😀
Seriously, I’m sure that’s possible but you’d need to figure out where the Woo Admin data goes inside the DB and add those delete statements to that
Hi,
Thanks for your posts!
How can i delete products with determitated date?, or products without images?
Thanks a lot!, sorry about my english bro
Hi bro, thanks so much for your comment! Yes, this is definitely possible, but I’m afraid it’s custom work. If you’d like to get a quote, feel free to contact me here. Thanks a lot for your understanding!
Hi
Thank you for this tuto 🙂
There’s an error #5
We have no ID in “wp_comments”, so impossible to delete the Ordre Note.
What ID we can take?
Olivier
Maybe “comment_ID”?
What about the download product permissions table ?
Not 100% sure. Have you found that out?
You’re missing a parenthasis in #2
Fixed, thanks!
Still getting an error on #2.
Sorry, try now
Can you bulk delete products associated with a certain category?
Hello Cor, thanks so much for your comment! Yes, this is definitely possible, but I’m afraid it’s custom work. If you’d like to get a quote, feel free to contact me here. Thanks a lot for your understanding!