SQLTeam.com | Weblogs | Forums

Delete from table when found in another table


#1

Hi There,
In need of your help please.

I have 2 tables, Table1 & Table2
I would like to search Table1 and for every record I find with a status = 9 use Table1.RecId to match a record in Table2 and when a match is found delete the record in Table2

Could you help me with how I would go about that.

I appreciate your help.

Best Regards,


#2
SELECT TestCol1, TestCol2, ...
-- DELETE D
FROM dbo.Table2 AS D
    JOIN dbo.Table1 AS T1
        ON T1.RecId  = D.RecId 
       AND T1.Status = 9

#3
DELETE t2
FROM
	#Table2 t2
WHERE 
	EXISTS (
			SELECT 
	        	1
	        FROM
	        	#Table1 t1
			WHERE 
				t2.RecID = t1.RecID
				AND t1.StatusID = 9
			);

#4

Thank you, that worked perfectly.

All the best.