how to delete completed orders in woocommerce using a my sql query

I want to delete completed all orders in woocommerce by using a single my sql query. Because, I’m having a problem with my WordPress Dashboard. I can’t view the completed orders from the back-end. It’s getting blank. I have 7,823 Completed Orders. I hope that’s why I seen white page when I’m going to view the Completed Orders.

Is there have a way to, That I can delete all Completed Orders using MySQL query. So, that I can run it in PHPMYADMIN.

Have any suggestions.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

My solution would be just deleting all the orders (If you’re moving from a shop with demo data to your new site). You can do this using the following SQL-queries.

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';

Method 2

To use the native Woocommerce functionality by the maximum, you can do it following way.
Move all orders to trash with SQL:

UPDATE wp_posts SET post_status = 'trash' WHERE post_type = 'shop_order';

And then go to Woocommerce -> Orders -> Trash and click Empty Trash.

Method 3

I solved this by first setting all the relevant orders to trash:

UPDATE wp_posts SET post_status = 'trash' WHERE post_type = 'shop_order';

If you have too many posts to get through when pressing Empty Trash and you get a PHP error, you can run the following SQL to remove them instead:

DELETE FROM wp_posts
WHERE post_type = 'shop_order' 
AND post_status = ‘trash’

Method 4

Woocommerce orders are stored in the post table, postmeta table, woocommerce_order_items, and woocommerce_order_itemmeta tables. Various parts of an order are stored in different tables. addition to that the order status is managed through the taxonomies which stores the order status list.
Anyone who is/will be writing this query have to write at least 5-6 queries minimum or
single large join query.

So, my suggestion to you should uninstall WooCoommerce, if you don’t have much larger product setup. To do this you can follow this link which will delete the data

Method 5

I just used this snippet with WP CLI from https://wordpress.org/support/topic/tip-how-to-delete-all-your-orders-and-save-you-time/#post-12290285

Warning: this deletes ALL ORDERS, you must adjust it for your case (only completed), please see: https://developer.wordpress.org/cli/commands/post/list/

wp post list --field=ID --post_type=shop_order --posts_per_page=2000 | xargs wp post delete --force

Worked with the official WooCommerce Subscriptions plugin, related subscriptions have also been deleted.

Method 6

I found this post while searching for “use wp cli to delete failed orders on woocommerce”, but Google sent me here. So I’m going to add my solution for those who may need it in the future!

You can easily do this with the WP CLI tool:

wp post delete $(wp post list --post_type=shop_order --post_status=wc-completed --format=ids)

If you’d like to delete and skip the trash, just add --force to end of the statement.

If you’d like to delete a different order status, here are the available options:

  • wc-completed
  • wc-pending
  • wc-processing
  • wc-on-hold
  • wc-cancelled
  • wc-refunded
  • wc-failed

Method 7

To delete orders with completed status, you should only use the following code.

UPDATE wp_posts 
SET post_status = 'trash' 
WHERE post_type = 'shop_order'
AND post_status = 'wc-completed'

This code does not completely eliminate the system or base of
Order data, send it to the trash.


Do you want to permanently delete orders ?, you must go to:

Woocommerce > Orders > Trash and click Empty Trash.

enter image description here


If you want to eliminate other orders, you should only know the different status that exists in Woocommerce:

  1. wc-completed
  2. wc-pending
  3. wc-processing
  4. wc-on-hold
  5. wc-cancelled
  6. wc-refunded
  7. wc-failed

And you are specifying them inside the clause IN (‘status’)

UPDATE wp_posts 
SET post_status = 'trash' 
WHERE post_type = 'shop_order'
AND post_status IN ('wc-pending', 'wc-processing', 'wc-on-hold', 'wc-cancelled', 'wc-refunded', 'wc-failed')


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x