Delete rows from a left join

Question

I want to join two tables and delete from them.

table1:

|ID| DATA|

table2:

|ID | ID_TAB1|

The query looks like this:

delete from table2 
using table2 as t2 left join table1 as t1 
on t2.ID_TAB1=t1.ID 
where t1.DATA='xxx';

The problem is that there is a third table:

table3:

|ID|ID_TAB2|

When I run my statement it fails saying

Update or deleting on table table2 violates foreign key contraint on table table3

I'm sure there are no data in the table3 connected to rows with t1.DATA='xxx' so why is there this error? Isn't my query correct?

Answer 1

This query:

delete from table2 
using table2 as t2 left join table1 as t1 
on t2.ID_TAB1=t1.ID 
where t1.DATA='xxx';

Is actually deleting all rows from table2 (or none if there is no match in the where. You have no connection between t2 and table2, so that is essentially a cross join.

As explained (deep down) in the documentation:

Do not repeat the target table in the using_list, unless you wish to set up a self-join.

Presumably, deleting all the rows is causing the problem with the third table. Note that the rows that you really do want to delete could also be causing the problem. In that case, you need to handle the errors using some sort of cascading logic.

You can express this using a using clause and removing the join:

delete from table2 t2
    using table1 t1 
    where t2.ID_TAB1 = t1.ID  and
          t1.DATA = 'xxx';

Or, just use exists in the where clause:

delete from table2 t2
    where exists (select 1
                  from table1 t1
                  where t1.id = t2.id_tab1 and t1.data = 'xxx'
                 );