Reputation: 71
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 ?
Upvotes: 0
Views: 63
Reputation: 2937
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)
Upvotes: 1
Reputation: 460108
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)
.
Upvotes: 2
Reputation: 6771
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.
Upvotes: 2