T-SQL:通过联接选择要删除的行

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]

是的,你可以。例子:

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

试图对Access数据库执行此操作,但发现我需要使用 A* 就在删除之后。

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

几乎是一样的 MySQL 但是你必须使用 表别名 在“删除”一词之后:

DELETE a
FROM TableA AS a
INNER JOIN TableB AS 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关键字)

我在用这个

DELETE TableA 
FROM TableA a
INNER JOIN
TableB b on b.Bid = a.Bid
AND [condition]

而且@thetxi方式也足够好,但是我阅读了答案和评论,我发现必须回答的一件事是在where子句中使用条件或作为连接条件。所以我决定测试它并写一个片段,但没有发现它们之间有什么有意义的区别。您可以在这里看到SQL脚本,重要的一点是,我更喜欢将它写为commnet,因为这不是确切的答案,但它很大,不能放在注释中,请原谅我。

Declare @TableA  Table
(
  aId INT,
  aName VARCHAR(50),
  bId INT
)
Declare @TableB  Table
(
  bId INT,
  bName VARCHAR(50)  
)

Declare @TableC  Table
(
  cId INT,
  cName VARCHAR(50),
  dId INT
)
Declare @TableD  Table
(
  dId INT,
  dName VARCHAR(50)  
)

DECLARE @StartTime DATETIME;
SELECT @startTime = GETDATE();

DECLARE @i INT;

SET @i = 1;

WHILE @i < 1000000
BEGIN
  INSERT INTO @TableB VALUES(@i, 'nameB:' + CONVERT(VARCHAR, @i))
  INSERT INTO @TableA VALUES(@i+5, 'nameA:' + CONVERT(VARCHAR, @i+5), @i)

  SET @i = @i + 1;
END

SELECT @startTime = GETDATE()

DELETE a
--SELECT *
FROM @TableA a
Inner Join @TableB b
ON  a.BId = b.BId
WHERE a.aName LIKE '%5'

SELECT Duration = DATEDIFF(ms,@StartTime,GETDATE())

SET @i = 1;
WHILE @i < 1000000
BEGIN
  INSERT INTO @TableD VALUES(@i, 'nameB:' + CONVERT(VARCHAR, @i))
  INSERT INTO @TableC VALUES(@i+5, 'nameA:' + CONVERT(VARCHAR, @i+5), @i)

  SET @i = @i + 1;
END

SELECT @startTime = GETDATE()

DELETE c
--SELECT *
FROM @TableC c
Inner Join @TableD d
ON  c.DId = d.DId
AND c.cName LIKE '%5'

SELECT Duration    = DATEDIFF(ms,@StartTime,GETDATE())

如果你能从这个脚本中得到好的理由或者写另一个有用的,请分享。谢谢,希望能帮上忙。

假设您有两个表,一个是主表集(例如雇员),另一个是子表集(例如从属表),您希望除去从属表中不能用主表中任何行进行键控的所有数据行。

delete from Dependents where EmpID in (
select d.EmpID from Employees e 
    right join Dependents d on e.EmpID = d.EmpID
    where e.EmpID is null)

这里要注意的一点是,您只是首先从join中收集一个empid的“数组”,使用该empid集对dependents表执行删除操作。

在sqlite中,唯一起作用的是类似于beauxjames的答案。

看来是这样的 DELETE FROM table1 WHERE table1.col1 IN (SOME TEMPORARY TABLE); 一些临时表可以通过选择并联接两个表来装箱,您可以根据要删除表1中记录的条件筛选这个临时表。

您可以运行此查询:

Delete from TableA 
from 
TableA a, TableB b 
where a.Bid=b.Bid
AND [my filter condition]

更简单的方法是:

DELETE TableA
FROM TableB
WHERE TableA.ID = TableB.ID