场景:
假设我有两个表，TableA和TableB。TableB的主键是一个单列(BId)，是TableA中的外键列。
在我的情况下，我想删除TableA中与TableB中特定行链接的所有行:我可以通过连接来做到这一点吗?删除从连接中拉入的所有行?
DELETE FROM TableA
FROM
TableA a
INNER JOIN TableB b
ON b.BId = a.BId
AND [my filter condition]
还是我被迫这样做:
DELETE FROM TableA
WHERE
BId IN (SELECT BId FROM TableB WHERE [my filter condition])
我问这个问题的原因是，在处理较大的表时，第一种选择似乎更有效。
谢谢!
当前回答
DELETE TableA
FROM TableA a
INNER JOIN TableB b
ON b.Bid = a.Bid
AND [my filter condition]
应该工作
其他回答
DELETE TableA
FROM TableA a
INNER JOIN TableB b
ON b.Bid = a.Bid
AND [my filter condition]
应该工作
是的，你可以。例子:
DELETE TableA
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]
试图用访问数据库做到这一点，发现我需要在删除后使用一个。*。
DELETE a.*
FROM TableA AS a
INNER JOIN TableB AS b
ON a.BId = b.BId
WHERE [filter condition]
我会使用这种语法
Delete a
from TableA a
Inner Join TableB b
on a.BId = b.BId
WHERE [filter condition]
上面的语法在Interbase 2007中不起作用。相反，我不得不使用类似的东西:
DELETE FROM TableA a WHERE [filter condition on TableA]
AND (a.BId IN (SELECT a.BId FROM TableB b JOIN TableA a
ON a.BId = b.BId
WHERE [filter condition on TableB]))
(注意Interbase不支持AS关键字作为别名)
