使用JOIN - SQL Server从表中删除行

Question

我有一个table_A -

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

有一个table_B -

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

我想从表B中删除表a中is_active值为0 OR null的行。因此，我正在考虑对id列进行INNER JOIN并应用WHERE子句。

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

我不希望在上述查询之后在表B中有任何额外的列或更改。这是正确的做法吗?

Answer 1

用exists:

代替JOIN

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

Answer 2

您需要说明要从哪个表中删除，如果有别名则使用别名:

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