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.

score 14 · Answer 1 · edited Apr 03 '18 at 01:35

14

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'

edited Apr 03 '18 at 01:35

Phill Tran

3
1

answered Mar 09 '18 at 07:23

Andrew Schultz

4,092
2
21
44

Thank You so much, it really works and works fasther rather manual cleaning in admin panel. – Bogdan Nov 13 '18 at 09:14
Tell me about it, my site would always time out when trying to do it via the front end. Saves you hours of frustration! – Andrew Schultz Nov 13 '18 at 10:01
2

Works great and so quick. Had 70k orders in the backend and some serious slowdown in filtering through them. Much better now. Run each one by one and backup first. – Dean Wilson Jun 01 '20 at 00:13
2

Should this also cover commentmeta? i.e., `DELETE FROM wp_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM wp_comments ); ` – BlueDogRanch Nov 11 '20 at 22:32
@BlueDogRanch yes it should, feel free to amend the code and add it in :) – Andrew Schultz Nov 12 '20 at 05:21

score 0 · Answer 2 · edited Mar 09 '18 at 08:07

0

If foreign key has been implemented in these tables then you have to first delete data from child table only when you will be able to delete data from parent table.

The SQL Query will be like below:

DELETE FROM wp_woocommerce_order_itemmeta
where Date(OrderDate Column) < '2017-01-01';

DELETE FROM wp_posts
WHERE post_type = 'shop_order' 
and Date(OrderDate Column) < '2017-01-01';

edited Mar 09 '18 at 08:07

LoicTheAztec

229,944
23
356
399

answered Mar 09 '18 at 07:42

Pankaj Kumar

550
2
6
22

Write an SQL query to remove old orders in Woocommerce

2 Answers2

Linked