從 INNER JOINed 3 個不同的表中刪除所有行

我有一個查詢。我想刪除這 3 個不同表中的所有選定行因為我有很多 INNER 連接，所以我想不通。我的目標是刪除這些賣家 ID 的所有內容。

SELECT *
FROM orders a
INNER JOIN order_items b ON a.order_id = b.order_id
INNER JOIN order_item_histories c ON c.order_item_id = b.order_item_id
WHERE a.seller_id IN (1, 3)

版本 Postgres 10.3

我試過這個，但我無法成功。

DELETE
FROM  
  USING orders
  USING order_items,
  USING order_item_histories
WHERE orders.order_id = order_items.order_id AND order_items.order_item_id = order_item_histories.order_item_id
AND  orders.seller_id IN (1, 3)

一條DELETE語句只能從一個表中刪除（除非有觸發器或外鍵ON DELETE CASCADE定義了選項）。

要克服這個問題，您可以使用 3 個DELETE語句：

DELETE FROM order_item_histories
WHERE order_item_id IN
     ( SELECT b.order_item_id 
       FROM order_items AS b
            JOIN orders AS a
              ON a.order_id = b.order_id
       WHERE a.seller_id IN (1, 3)
     )
RETURNING order_item_id, order_item_history_id ;

DELETE FROM order_items 
WHERE order_id IN 
     ( SELECT order_id
       FROM orders
       WHERE seller_id IN (1, 3)
     )
RETURNING order_id, order_item_id ;

DELETE FROM orders
WHERE seller_id IN (1, 3)
RETURNING order_id ;

或通過連結（數據修改）CTE：

WITH 
 a AS 
 ( DELETE FROM orders
   WHERE seller_id IN (1, 3)
   RETURNING order_id
 ),
 b AS
 ( DELETE FROM order_items 
   WHERE order_id IN (SELECT order_id FROM a)
   RETURNING order_id, order_item_id 
 ),
 c AS
 ( DELETE FROM order_item_histories
   WHERE order_item_id IN (SELECT order_item_id FROM b)
   RETURNING order_item_id, order_item_history_id
 )
SELECT
   a.order_id,
   b.order_item_id, 
   c.order_item_history_id
FROM a
 LEFT JOIN b ON a.order_id = b.order_id
 LEFT JOIN c ON c.order_item_id = b.order_item_id ;

引用自：https://dba.stackexchange.com/questions/203350