Delete rows from table using JOIN - SQL Server

Question

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?

Gordon Linoff · Accepted Answer · 2021-02-18 19:04:37Z

2

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

edited Feb 18, 2021 at 19:04

answered Feb 18, 2021 at 18:03

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kev Over a year ago

are you missing a line from your code? because i don't see the exists part

SMor Over a year ago

It should immediately follow WHERE (e.g., DELETE ... WHERE EXISTS (SELECT 1 ...)

kev Over a year ago

i'm getting SQL Error [102] [S0001]: Incorrect syntax near ')' on the last line for some reason. Using the exact query as above but with DELETE .. WHERE EXISTS (SELECT 1...);

Charlieface · Accepted Answer · 2021-02-18 20:19:49Z

0

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

answered Feb 18, 2021 at 20:19

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

Delete rows from table using JOIN - SQL Server

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related