patternsqlModerate
Deleting all rows from INNER JOINed 3 different tables
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.
Version Postgres 10.3
I tried this but I couldn't succeed.
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:
Reference
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.