Delete records from multiple tables using Join with PostgreSQL

Question

I'm using PostgreSQL 11 database, I have Three related tables by foreign keys, T1, T2, and T3. There is a Many to Many relationship between T1 and T2, so I have used T3 as a joining table. I'm trying to delete records from these three tables with one query using JOIN, I have tried:

DELETE t1, t3, t2 FROM T1 AS t1 
INNER JOIN T3 AS t3 ON t1.id = t3.t1_id 
INNER JOIN T2 AS t2 ON t2.id = t3.t2_id 
WHERE t1.col = 'something';

I ran this query using pgAdmin, it returns:

ERROR:  ERROR: syntax error near « t1 »
LINE 1: DELETE t1, t3, t2 FROM T1 ...

What is the problem with my query syntax here? Am I missing something?

You can only delete from one table at a time in Postgres.

Gordon Linoff
– Gordon Linoff

2020-09-15 13:55:45 +00:00
Commented Sep 15, 2020 at 13:55 — Gordon Linoff
– Gordon Linoff, Commented Sep 15, 2020 at 13:55

Gordon Linoff · Accepted Answer · 2020-09-15 13:57:46Z

3

Instead, delete from one table at a time:

with t1 as (
      delete t1 
      where t1.col = 'something'
      returning *
     ),
     t3 as (
      delete t3
      where t3.t1_id in (select id from t1)
      returning *
     )
delete t2
where t2.id in (select t2_id from t3);

This is not exactly the same. The inner join requires matches among the tables. But I think this is your intention.

answered Sep 15, 2020 at 13:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

swimmer Over a year ago

Shouldn't the delete statements be delete from statements?

Collectives™ on Stack Overflow

Delete records from multiple tables using Join with PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related