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.

2 Answers 2

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.

2
  • This answer did the job for me to delete all orders prior to a specific date 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
    – Leo Fisher
    Oct 8, 2023 at 14:28
2

Try wp_posts instead of wp_post.

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 .
    – Denoteone
    Feb 4, 2019 at 21:07

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.