4

I have a query. I would like to delete all selected rows in these 3 different tables Since I have a lot of INNER joins, I couldn't figure it out. My goal is to delete everything for these seller_ids.

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)

Version Postgres 10.3

I tried this but I couldn't succeed.

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)
6
  • on delete cascade?
    – Imanol Y.
    Commented Apr 8, 2018 at 17:37
  • @lmanol Y. Unfortunately I don't have foreign keys among these tables. There are some reasons for that so that I am looking for a way around.
    – SNaRe
    Commented Apr 8, 2018 at 17:42
  • Are the INNER joins intentional or you meant to write LEFT joins? In other words, do you want to delete from table a only when it has related rows in tables b and c? Commented Apr 8, 2018 at 18:11
  • @ypercubeᵀᴹ When all 3 tables and conditionmatches, I want to delete all matched from all 3. So that if only matches in orders table doesn't work for. It must match condition in 3 of them at once.
    – SNaRe
    Commented Apr 8, 2018 at 18:43
  • So you mean that if there are orders for say seller 1 but no order_items or orders and order_items but no order_items_histories, then you don't want to delete anything? Commented Apr 8, 2018 at 19:21

2 Answers 2

5

A DELETE statement can only delete from one table (unless there are triggers or foreign keys with ON DELETE CASCADE options defined).

To overcome the problem you can either use 3 DELETE statements:

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 ;

or by chaining (data modifying) CTEs:

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 ;
1
  • I commented on the first post. Can you please check it makes any difference with this SQL? Thank you.
    – SNaRe
    Commented Apr 8, 2018 at 18:45
12

If you have more than one join you could use comma separated USING statements:

DELETE 
FROM 
      AAA AS a 
USING 
      BBB AS b,
      CCC AS c
WHERE 
      a.id = b.id 
  AND a.id = c.id
  AND a.uid = 12345 
  AND c.gid = 's434sd4'

Reference

3
  • 3
    Probably not selected as the answer because this doesn't delete from all three tables, but this approach with 3 different statements would be more elegant Commented Aug 14, 2019 at 15:42
  • If you want to delete from multiple tables, add the table name in the from column, and remove that from using table
    – Mahesh
    Commented Aug 14, 2019 at 23:39
  • 2
    This was useful to me as I was looking for multiple joins lol
    – A. L
    Commented Apr 7, 2020 at 8:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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