Write an SQL query to remove old orders in Woocommerce

Question

I would like to remove some OLD order data in Woocommerce, keeping only the last 12 months of order data

This is the SQL query that I use to remove 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'

What would I need to add to REMOVE all order data BEFORE 1st January 2017 and KEEP Orders data since 1 Jan 2017?

I don't know so much about SQL, as I have used a few SQL queries over the years.

Any help is appreciated.

I'm amazed there isn't already a plugin for this and also to remove customers with no orders. I doubt I'm the only one trying to keep their WP/WC database clean.

Answer 1

This is the general idea, you delete the children items first and then remove the parents last. DO NOT RUN THIS WITHOUT TESTING FIRST. I accept no responsibility for lost data.

DELETE 
FROM    wp_woocommerce_order_itemmeta 
WHERE   order_item_id IN (
    SELECT  order_item_id
    FROM    wp_woocommerce_order_items
    WHERE   order_id IN (
        SELECT  ID 
        FROM    wp_posts
        WHERE   post_date < '2017-01-01'
    )
)

DELETE 
FROM    wp_woocommerce_order_items
WHERE   order_id IN (
    SELECT  ID 
    FROM    wp_posts
    WHERE   post_date <= '2017-01-01'
)

DELETE 
FROM    wp_comments 
WHERE   comment_type = 'order_note'
AND     comment_post_ID IN (
    SELECT  ID 
        FROM    wp_posts
        WHERE   post_date <= '2017-01-01'
)

DELETE 
FROM    wp_postmeta 
WHERE   post_id IN ( 
    SELECT  ID 
    FROM    wp_posts 
    WHERE   post_type = 'shop_order'
    AND     post_date <= '2017-01-01' 
)

DELETE 
FROM    wp_posts 
WHERE   post_type = 'shop_order'
AND     post_date <= '2017-01-01'