DELETE rows in multiple tables using JOIN returning error

Question

I spent some time in my WP 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 order data from all of the different tables.

But when I run the following query:

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"

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.

I did try removing the "" but am still getting a similar error. If I remove " FROM" JOIN in the next line is underlined with Red and throws a similar error. — Denoteone
– Denoteone, Commented Feb 4, 2019 at 20:19
Multi-table DELETE requires that you list the tables you want to delete from between DELETE and FROM. — Barmar
– Barmar, Commented Feb 4, 2019 at 20:27
I see. that is what @scaisEdge just mentioned below. I will try that. — Denoteone
– Denoteone, Commented Feb 4, 2019 at 20:27

ScaisEdge · Accepted Answer · 2019-02-04 20:30:19Z

1

Try adding the table name of the table you want delete rows

DELETE wp_post  
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"

and for deleting in more then a table add the tables name in DELETE clause
eg for wp_post and wp_postmeta use

DELETE wp_post, wp_postmeta  
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"

edited Feb 4, 2019 at 20:30

answered Feb 4, 2019 at 20:24

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Denoteone Over a year ago

So if all of the tables in my JOIN statement will have rows removed from them should I also include those? DELETE wp_post wp_postmeta wp_woocommerce_order_items wp_woocommerce_order_itemmeta does that make sense.

ScaisEdge Over a year ago

In the DELETE clause you must set the name of the tables where you want delete rows .. if you want delete form more then a table you must add the table in this clause .. yes .. Then all the matching rows wull be delete in the tables you indicate .. .. answer updated ..

Denoteone Over a year ago

Thanks that was it. Once I added a comma seperated list of all the tables to be edited and it worked like a charm.

Collectives™ on Stack Overflow

DELETE rows in multiple tables using JOIN returning error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related