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!
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';
2. Bulk Delete All Products
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
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
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
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';
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!