mysql - 使用 JOIN 返回错误删除多个表中的行

Question

我花了一些时间在我的 WP 数据库中试图弄清楚如何清理一些已完成的订单数据。下面是我相信将有助于从所有不同表中删除订单数据的查询。

但是当我运行以下查询时:

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"

我收到以下 MySQL 错误:

#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

我没有正确加入我的 WordPress 表格吗？

这个问题可能更适合 stackoverflow，但我想我会先在这里尝试。

Answer 1

尝试添加要删除行的表的表名

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"

要删除多个表，请在 DELETE 子句中添加表名
例如对于 wp_post 和 wp_postmeta 使用

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"