Postgresql – How to delete data from both tables or least one of them, when using “delete from … using … where” in Postgresql

deletejoin;postgresql

I have this sql script for my Postgresql db:

  DELETE FROM table1
  USING table2
  WHERE table2.frg_table1_id = table1.id
  AND table1.id = $1

I want to it to always delete data from table1 if the condition table1.id = $1 is met. And, optionally, delete corresponding data from table2 too. Even when corresponding data in table2 doesn't exist, it still must delete from table1

An issue now is that, it'll delete data either from both tables or from none.

How should I twist my script?

Best Answer

A single DELETE statement only deletes rows from a single table (ignoring table inheritance for now). So if you want to delete from two tables, you have to run two delete statements.

You can do it in "one" statement, but using a common table expression that first deletes from table2, then from table1:

with t2_deleted as (
  delete from table2
  where frg_table1_id = $1
)
delete from table1
where id = $1;

But in the end, this is exactly the same as simply running two DELETE statements in a single transaction:

delete from table2
where frg_table1_id = $1;

delete from table1
where id = $1;
Related Question