postgres delete from indirectly related tables

Question

I have TableA that can have many TableB related to it and TableB can have many TableC rows related to it which TableC has many TableD rows. So TableA -> TableB -> TableC -> TableD. What I want to do is given the TableA row id, select all the TableD rows that are related through other tables and delete all of them.

TableA   |   TableB |   TableC |   TableD
--------------------------------------
tableAId | tableAId | tableBId | tableCId

         | tableBId | tableCId | tableDId

and the query i've tried:

DELETE FROM TableD
 WHERE TableA.tableAId = 2
 AND TableA.tableAId= TableB.tableAId
 AND TableB.tableBId= TableC.tableBId
 AND TableC.tableCId = TableD.tableCId

sample table

TableA        TableB                     TableC                TableD

tableAID | tableAID  tableBId | tableBId   tableCId | tableCId   tableDId
---------|--------------------|---------------------|---------------------
 1       | 1            15    |  15         5       | 6              4
 2       | 2           16     |  16         6       | 5              3
                                                      5              14
                                                      5              11

Desired result, Given tableAId = 1

TableA        TableB                     TableC                TableD

tableAID | tableAID  tableBId | tableBId   tableCId | tableCId   tableDId
---------|--------------------|---------------------|---------------------
 1       | 1            15    |  15         5       | 6              4
 2       | 2           16     |  16         6       |

Your query almost done. Just add to it the list of using tables: DELETE FROM TableD USING TableA, TableB, TableC WHERE ... More about USING clause in the documentation: postgresql.org/docs/current/static/sql-delete.html — Abelisto
– Abelisto, Commented Sep 11, 2018 at 19:40

user10343866 · Accepted Answer · 2018-09-11 19:07:44Z

1

Use the following subquery to get all tableCIds that are related to your given tableAId:

SELECT DISTINCT TableC.tableCId
FROM TableC
LEFT JOIN TableB ON TableC.tableBId = TableB.tableBId
WHERE TableB.tableAId = 2

You can now use this subquery in your delete command, which should work somehow like this:

DELETE FROM tableD
USING ( ... ) sub
WHERE tableD.tableCId IN (SELECT * FROM sub)

answered Sep 11, 2018 at 19:07

user10343866

Sign up to request clarification or add additional context in comments.

Comments

Evan Carroll · Accepted Answer · 2018-09-11 18:59:00Z

0

Something like this,

DELETE
FROM TableD AS inner
WHERE EXISTS (
  SELECT
  FROM tableD AS outer
  JOIN tableC
    USING (tableDid)
  JOIN tableB
    USING (tableBid)
  JOIN tableA
    USING (tableAid)
  WHERE tableAId = 2
   AND outer.tableDid = inner.tableDid
);

answered Sep 11, 2018 at 18:59

Evan Carroll

1

2 Comments

qaakmnd Over a year ago

That worked, just had to change the inner and outer names and where you had USING (tableDid) shoulda be USING (tableCid)

user330315 Over a year ago

Don't you need to quote "outer" as it is a reserved keyword?

Collectives™ on Stack Overflow

postgres delete from indirectly related tables

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related