1

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)
  • on delete cascade? – Imanol Y. Apr 8 '18 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 Apr 8 '18 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? – ypercubeᵀᴹ Apr 8 '18 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 Apr 8 '18 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? – ypercubeᵀᴹ Apr 8 '18 at 19:21
4

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

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

  • 1
    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 – Eric Parshall Aug 14 '19 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 Aug 14 '19 at 23:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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