1

I spent some time in my DB trying to figure out how I can clean out some completed order data. Below is the query that I am confident will help remove the data from completed woocommerce orders.

My question is more of a DB Query one:

DELETE * FROM wp_post
JOIN wp_postmeta ON wp_post.ID = wp_postmeta.post_id
JOIN wp_woocommerce_order_items ON wp_postmeta.post_id =  wp_woocommerce_order_items.order_item_id
JOIN wp_woocommerce_order_itemmeta ON wp_postmeta.post_id = wp_woocommerce_order_itemmeta.order_item_id
WHERE wp_post.post_type = "shop_order" 
AND wp_post.post_status = "wc-completed"

When I run the above query I get the following MySQL error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '* FROM wp_post
JOIN wp_postmeta ON wp_post.ID = wp_postmeta.post_id
JOIN wp_wo' at line 1

Am I not JOIN my WordPress Tables correctly?

This question may be more appropriate for stackoverflow but figured I would try here first.

Below is the correct query string:

DELETE wp_posts, wp_postmeta, wp_woocommerce_order_items, wp_woocommerce_order_itemmeta FROM 
JOIN wp_posts.ID = wp_postmeta.post_id, 
wp_postmeta.post_id = wp_woocommerce_order_itemmeta.order_item_id,
wp_posts.ID = wp_woocommerce_order_items.order_item_id
WHERE wp_posts.post_type = "shop_order" 
AND wp_posts.post_status = "wc-completed"

Which fixed the wp_posts not wp_post and included all of the tables in-between the DELETE and FROM.

Improve this question

3 Answers 3

Reset to default
5

Here is my solution for delete WordPress WooCommerce orders with SQL:

DELETE wp_posts, wp_postmeta, wp_woocommerce_order_items, wp_woocommerce_order_itemmeta
FROM wp_posts
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
LEFT JOIN wp_woocommerce_order_itemmeta ON wp_postmeta.post_id = wp_woocommerce_order_itemmeta.order_item_id
LEFT JOIN wp_woocommerce_order_items ON wp_posts.ID = wp_woocommerce_order_items.order_item_id
WHERE wp_posts.post_type = "shop_order" 
AND wp_posts.post_date < '2019-03-01';

Maybe you should edit the table prefixes (wp_) and the date at the last line.

Improve this answer
2
  • This answer did the job for me to delete all orders prior to a specific date Commented May 3, 2023 at 11:31
  • if you have more then 1 million Orders i recommend adding another AND clause to go between dates since this query will delete exponential records Commented Oct 8, 2023 at 14:28
2

Try wp_posts instead of wp_post.

Improve this answer
1
  • 1
    Thanks @mzykin that was one of my issues. I also had to list all of the tables that are going to have rows removed in-between DELETE and FROM. Those two fixes cleared up my error and the query ran successfully . Commented Feb 4, 2019 at 21:07
0

All of the answers here have forgotten a small piece of data that will still be left behind in the database ... Order Notes (wp_comments with type order_note) will remain.

The following query will successfully delete orders (before 2021-01-01) and ALL the data associated with each order:

DELETE wp_posts, wp_postmeta, wp_comments, wp_woocommerce_order_items, wp_woocommerce_order_itemmeta
FROM wp_posts
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
LEFT JOIN wp_comments ON wp_posts.ID = wp_comments.comment_post_ID
LEFT JOIN wp_woocommerce_order_items ON wp_posts.ID = wp_woocommerce_order_items.order_id
LEFT JOIN wp_woocommerce_order_itemmeta ON wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
WHERE wp_posts.post_type = 'shop_order' AND `post_date_gmt` < '2021-01-01'

You can add any other WHERE data to achieve more:

for example, by date range (instead of before given date)

WHERE wp_posts.post_type = 'shop_order' AND `post_date_gmt` BETWEEN '2021-01-01' AND '2021-12-31'

for example by Order Id:

WHERE wp_posts.post_type = 'shop_order' AND wp_posts.ID = 278853

or ALL orders (be careful)

WHERE wp_posts.post_type = 'shop_order'
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.