SQL Server – Delete rows from one side of a JOIN

I have a query that identifies orphaned rows in one table after joining it to another. The query I have for selecting the rows that need to be deleted works fine, and properly selects the rows that need to be deleted:

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

My question is, now that I’ve selected which rows need to be deleted, how do I use this join to actually delete the rows from Route_Legs_Route_Header?

Answer

you should write the query as:

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

The From clause with the join operator will be evaluated first. Then the Delete clause will delete the rows from rh alias i.e. the table from the join Route_Legs_Route_Header. Hope this helps!!