Search code examples
sqlpostgresqljoinsql-deleteouter-join

PostgreSQL delete rows that outer join from multiple tables

I am trying to delete rows from table where theire IDs doesnt exist in other 2 tales. on PostgreSQL :

table A :

idB idC age
1 4 Three
2 5 Three
3 6 Three

table B :

idB name age
3 Two Three
7 Two Three

table C :

idC name age
4 Two Three
5 Two Three
6 Two Three

final table A :

idB idC age
3 6 Three

first row of table A should be deleted because idC = 4 doesnt exist in table C Second row of table A should be deleted because idB = 2 doesnt exist in table B Third row of table A should be kept idB = 3 exists in table B and idC = 6 exists in table C

How can I do that?

Solution

  • Simply use not exists:

    delete from tableA a
    where not exists (select 1 from tableB b where b.idB = a.idB) or
          not exists (select 1 from tableC c where c.idC = a.idC);