代码之家  ›  专栏  ›  技术社区  ›  Denoteone

使用联接返回错误删除多个表中的行

  •  1
  • Denoteone  · 技术社区  · 5 年前

    我花了一些时间在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,但我想我会先在这里尝试。

    1 回复  |  直到 5 年前
        1
  •  1
  •   ScaisEdge    5 年前

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

    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-posteta

    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"