SQL: Selecting rows to delete via joins

2018-07-03 19:45:48

Scenario:

Let's say I have two tables, TableA and TableB. TableB's primary key is a single column (BId), and is a foreign key column in TableA.

In my situation, I want to remove all rows in TableA that are linked with specific rows in TableB: Can I do that through joins? Delete all rows that are pulled in from the joins?

DELETE FROM TableA 
FROM
   TableA a
   INNER JOIN TableB b
      ON b.BId = a.BId
      AND [my filter condition]

Or am I forced to do this:

DELETE FROM TableA
WHERE
   BId IN (SELECT BId FROM TableB WHERE [my filter condition])

The reason I ask is it seems to me that the first option would be much more effecient when dealing with larger tables.

Thanks!

DELETE TableA
FROM   TableA a
       INNER JOIN TableB b
               ON b.Bid = a.Bid
                  AND [my filter condition]

应该管用

我会用这个语法

Delete a 
from TableA a
Inner Join TableB b
on  a.BId = b.BId
WHERE [filter condition]

Yes you can. Example :

DELETE TableA 
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]

链接地址: http://www.djcxy.com/p/94220.html

上一篇: 如何在另一个表中选择没有匹配条目的行？

下一篇: SQL：选择要通过连接删除的行