How to remove rows from a table A that has no relationship with table B?

4

I have 2 tables and want to remove rows from table A that have no relationship with table B?

For example: In table B I have a field FK_ID and I want to remove from table A all rows that have no relationship with table B, ie, it does not have an A.FK_ID = B.FK_ID

    
asked by anonymous 25.01.2017 / 18:46

2 answers

3

Here's a way to do it:

DELETE FROM TABELA_A where TABELA_A.FK_ID not in (select FK_ID FROM TABELA_B)
    
25.01.2017 / 18:53
2

Using NOT EXISTS

DELETE t_a
  FROM a t_a
 WHERE NOT EXISTS(SELECT 1
                    FROM b t_b
                   WHERE t_b.fk_id = t_a.fk_id)

Using JOIN

DELETE t_a
  FROM a t_a
  LEFT JOIN b t_b ON t_b.fk_id = t_a.fk_id
 WHERE b.fk_id IS NULL
    
25.01.2017 / 19:05