SQL Server语言 - 从 JOIN 的一侧删除行

select  fl.Request_ID as Check_RID, 
        fl.J_Number as Check_JID, 
        rh.request_ID, 
        rh.J_Number
from        Route_Legs_Route_Header rh
left join   Request_Route_Legs fl on fl.Request_ID = rh.Request_ID AND fl.J_Number = rh.J_Number
where   rh.Route_ID <> 0
AND     fl.Request_ID is null

在 SQL Server 中，可以从表别名中删除。当您已经有一个有效的查询时，这会派上用场，就像您一样：

delete rh
from Route_Legs_Route_Header rh
left join Request_Route_Legs fl 
    on fl.Request_ID = rh.Request_ID and fl.J_Number = rh.J_Number
where rh.Route_ID <> 0 and fl.Request_ID is null

您应该将查询编写为：

DELETE FROM rh
FROM Route_Legs_Route_Header AS rh
left join  Request_Route_Legs fl on fl.Request_ID = rh.Request_ID 
AND fl.J_Number = rh.J_Number
where   rh.Route_ID <> 0
AND     fl.Request_ID is null

将首先计算带有连接运算符的 From 子句。然后 Delete 子句将从别名中删除行rh即从连接Route_Legs_Route_Header中删除表。 希望这有帮助！！

如果我是你，我会使用一个诱惑表，如下所示：

select  fl.Request_ID as Check_RID, 
        fl.J_Number as Check_JID, 
        rh.request_ID, 
        rh.J_Number
INTO #RowsToDelete
from        Route_Legs_Route_Header rh
left join   Request_Route_Legs fl on fl.Request_ID = rh.Request_ID AND fl.J_Number = rh.J_Number
where   rh.Route_ID <> 0
AND     fl.Request_ID is null
DELETE FROM Route_Legs_Route_Header
WHERE request_ID in (Select Request_ID from #temp)

在这种情况下，我更喜欢可更新的 cte：

首先，我创建一个模型场景来模拟您的问题：

DECLARE @tblParent TABLE(ID INT IDENTITY,SomeValue VARCHAR(100));
INSERT INTO @tblParent VALUES('Row 1'),('Row 2');
DECLARE @tblChildren TABLE(ID INT IDENTITY, ParentID INT,SomeValue VARCHAR(100));
INSERT INTO @tblChildren VALUES(1,'Child 1.1'),(1,'Child 1.2')
                              ,(2,'Child 2.1')
                              ,(3,'Orphan');

--此查询原则上是您的方法：
--将表别名ch添加到*中，以仅从@tblChildren减少行上的集合。

SELECT * 
FROM @tblChildren ch
LEFT JOIN @tblParent p ON p.ID=ch.ParentID
WHERE p.ID IS NULL;

--这个查询 - 至少对我来说 - 更容易阅读，并且做同样的事情：

SELECT * FROM @tblChildren ch
WHERE NOT EXISTS(SELECT 1 FROM @tblParent p WHERE p.ID=ch.ParentID);

--我们可以将最后一个查询放入 cte 的主体并删除其结果集：

WITH cte AS
(
    SELECT * FROM @tblChildren ch
    WHERE NOT EXISTS(SELECT 1 FROM @tblParent p WHERE p.ID=ch.ParentID)
)
DELETE FROM cte;

--看看吧，孤儿不见了：

SELECT * FROM @tblChildren ch
WHERE NOT EXISTS(SELECT 1 FROM @tblParent p WHERE p.ID=ch.ParentID);