CODEWITHSUNDEEP
sqlsql-servert-sql
KWallace
KWallace

Reputation: 632

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?

Upvotes: 1

Views: 459

Answers (4)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67301

I prefer an updatable cte in such cases:

First I create a mockup scenario to simulate your issue:

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');

--This query is - in principles - your approach:
--Add the table alias ch to the * to reduce the set on rows solely from @tblChildren.

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

--This query is - at least for me - easier to read and does the same:

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

--We can place the last query into a cte's body and delete its resultset:

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

--Check it out, the orphan is gone:

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

Upvotes: 0

Deepshikha
Deepshikha

Reputation: 10264

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!!

Upvotes: 0

GMB
GMB

Reputation: 222442

In SQL Server, you can delete from a table alias. This comes handy when you already have a working query, like you do:

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

Upvotes: 3

Eray Balkanli
Eray Balkanli

Reputation: 7960

I'd use a temptable if I were you like below:

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)

Upvotes: 0

Related Questions