0

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.

1

Try wp_posts instead of wp_post.

  • 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 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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