PostgreSQL delete rows that outer join from multiple tables

Question

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?

Gordon Linoff · Accepted Answer · 2021-04-10 01:52:03Z

1

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);

answered Apr 10, 2021 at 1:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

eshirvana · Accepted Answer · 2021-04-10 01:37:45Z

1

here is how you can do it:

with tt as (
  select a.* from tableA a
  left join tableB b on a.idb =b.idb
  left join tableC c on a.idC = c.idc
  where b.idb is null or c.idc is null
)
delete from tableA a
using tt 
where a.idB = tt.idB
and a.idC = tt.idC

answered Apr 10, 2021 at 1:37

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Collectives™ on Stack Overflow

PostgreSQL delete rows that outer join from multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related