UPDATE: 10/27/2022
After some feedback from some readers, it has come to my attention that the order of operations has to be updated. Originally we deleted all the posts and then ran some cleanup. However, the queries for cleanup won’t work because the posts will have already been deleted. I’ve updated the post so that these changes are reflected.
We were recently contracted to assist in a small portion of migrating a WooCommerce installation to a new server. While there were a lot of unseen struggles with the migration plan we need to purge a specific range of orders from the new system. Below you will find the necessary query to accomplish just that!
The Query
Before you run this query it is IMPERATIVE that you have a backup of your database. 45Squared is not responsible for any data loss or unwanted results. If you are unsure of what you are doing PLEASE contact us for help.
First, delete all of the order details and metadata. This will be sure that there isn’t any orphaned data in your database.
DELETE FROM wp_woocommerce_order_items WHERE order_id IN ( SELECT ID FROM wp_posts WHERE post_date >= '2019-01-01')
DELETE FROM wp_comments WHERE comment_type = 'order_note' AND comment_post_ID IN ( SELECT ID FROM wp_posts WHERE post_date >= '2019-01-01')
DELETE FROM wp_postmeta WHERE post_id IN ( SELECT ID FROM wp_posts WHERE post_type = 'shop_order' AND post_date >= '2019-01-01')
Some Light Cleanup
Second, delete all of the posts within your range.
DELETE FROM wp_posts WHERE post_type = 'shop_order' AND post_date BETWEEN '2019-01-01' AND '2019-04-04';
Once again you could do a range of orders by changing the post_date >= '2019-01-01' in each query to post_date BETWEEN '2019-01-01'
Need help with your WooCommerce installation? Contact us today and let’s see what we can do to help!
