How to remove data from table if the ID is in the column

Question

I have to write the script that will pull IDs of all members that email has been hard bounced. In order to do it I wrote this

 Select id FROM Members m
 Join tbl_memberlanguageid mli on m.ID = mli.MLI_MemberID 
 Where Cast(dateCreated as date) >= '01-Dec-2014'
 and mli.MLI_LanguageID = 3 
 and EmailHardBounces = 1

Then I need to use this list of IDs to remove them from the table Members. How can I write this script so that it removes everything related to ID from Members if the ID is in that list ?

Answer 1

Another way:

DELETE FROM Members WHERE ID IN(Select id FROM Members m
Join tbl_memberlanguageid mli on m.ID = mli.MLI_MemberID 
Where Cast(dateCreated as date) >= '01-Dec-2014'
and mli.MLI_LanguageID = 3 
and EmailHardBounces = 1)

Answer 2

You can just change the Select id to Delete m:

BEGIN TRAN

DELETE  m
FROM    Members m
        JOIN tbl_memberlanguageid mli ON m.ID = mli.MLI_MemberID
WHERE   CAST(dateCreated AS DATE) >= '01-Dec-2014'
        AND mli.MLI_LanguageID = 3
        AND EmailHardBounces = 1

ROLLBACK
COMMIT

Just confirm the record count is correct before committing the delete.

Answer 3

I like CTE's since they allow to see what i'm going to delete:

WITH MemberIdsToDelete AS
(
     Select id FROM Members m
     Join tbl_memberlanguageid mli on m.ID = mli.MLI_MemberID 
     Where Cast(dateCreated as date) >= '01-Dec-2014'
     and mli.MLI_LanguageID = 3 
     and EmailHardBounces = 1
)
SELECT id FROM MemberIdsToDelete

If you finally want to delete them replace

SELECT id FROM MemberIdsToDelete

with

DELETE FROM Members WHERE id IN (SELECT id FROM MemberIdsToDelete).