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.

DELETE
FROM
wp_posts
WHERE
post_type = 'shop_order'
AND post_date >= '2019-01-01';

This query will delete all posts greater than a certain date. You could also modify it to do a range of dates by changing the query slightly.

DELETE
FROM
wp_posts
WHERE
post_type = 'shop_order'
AND post_date BETWEEN '2019-01-01' AND '2019-04-04';

Some Light Cleanup

Once you have deleted the posts you will need to do a bit of housekeeping just to keep your database free of unwanted data.

DELETE
FROM
wp_woocommerce_order_items
WHERE
order_id IN (
SELECT
ID
FROM
wpbuyterps_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'
)


SELECT
FROM
wp_postmeta
WHERE
post_id IN (
SELECT
ID
FROM
wp_posts
WHERE
post_type = 'shop_order'
AND post_date >= '2019-01-01'
)

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!

Connect with 45Squared