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

2 Views

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.

Read More

Have any suggestions.

Post Views: 2

Related posts

Leave a Reply

7 comments

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

  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.

  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’

  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

  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.

  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

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