Delete rows from table using JOIN – SQL Serv…

I have a table_A –

id  |   name  |  is_active |
----+---------+------------+
1   |  jon    |    1       |
2   |  ham    |    0       |
3   |  gary   |   null     |

I have a table_B –

id  |   name  |
----+---------+
1   |  jon    |
2   |  ham    |

I want to remove rows from table B that have is_active value as 0 OR null in table A. So I’m thinking about an INNER JOIN on id column and applying a WHERE clause.

DELETE ROWS from table_B B
INNER JOIN table_A A ON B.id = A.id
WHERE A.is_active = 0 OR A.is_active IS NULL

I don’t want any additional columns or changes in table B after the above query. Is this the right way to do it?

Answer

Instead of JOIN, use exists:

DELETE FROM table_B
    WHERE EXISTS (SELECT 1
                  FROM table_A A
                  WHERE A.id = table_B.id AND
                        (A.is_active = 0 OR A.is_active is null)
                 );

Delete rows from table using JOIN – SQL Server

Advertisement

Answer