CODEWITHSUNDEEP
sqlsql-server
kev
kev

Reputation: 2881

Delete rows from table using JOIN - SQL Server

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?

Upvotes: 0

Views: 735

Answers (2)

Charlieface
Charlieface

Reputation: 71467

You need to say which table you want to delete from, use the alias if you have one:

DELETE B
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);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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)
                 );

Upvotes: 2

Related Questions