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?

Answer 1

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.