HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Deleting all rows from INNER JOINed 3 different tables

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
rowsdeletingtablesalljoineddifferentfrominner

Problem

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)

Solution

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

Code Snippets

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'

Context

StackExchange Database Administrators Q#203350, answer score: 12

Revisions (0)

No revisions yet.