我正在使用这个
DELETE TableA
FROM TableA a
INNER JOIN
TableB b on b.Bid = a.Bid
AND [condition]
@TheTXI的方法已经足够好了,但是我阅读了答案和评论,发现必须回答的一件事情是在WHERE子句或作为连接条件中使用条件。所以我决定测试并编写代码片段,但是没有发现它们之间有什么实质性的区别。你可以在这里看到SQL脚本,重要的一点是我更喜欢将其写成注释,因为这不是确切的答案,但它很大,不能放在注释中,请谅解。
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
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
FROM @TableC c
Inner Join @TableD d
ON c.DId = d.DId
AND c.cName LIKE '%5'
SELECT Duration = DATEDIFF(ms,@StartTime,GETDATE())
如果您能从这个脚本中得到好的理由或编写另一个有用的脚本,请分享。谢谢,希望这可以帮助您。
DELETE TableA, TableB ...
的语法来运行一个查询,这实际上删除了两个表中的相关记录。不错。 - AndrewDELETE from TableA a using TableB b where b.Bid = a.Bid and [我的筛选条件]
- bartolo-otritDELETE a FROM TableA a INNER JOIN TableB b on b.Bid = a.Bid and [my filter condition]
- masam