Delete one row based on two other tables

Question

I am trying to delete the rows as follows:

DELETE MemberDetails
WHERE Exists (
    SELECT m.MemberID
    FROM MemberDetails m
    INNER JOIN PaymentDetails pd ON
    pd.MemberID = m.MemberID
    INNER JOIN GDPRCompliance gd ON
    gd.MemberID = m.MemberID
    WHERE MActive= 'N' AND OptionB= 'N' and DATEDIFF(dd,ExpiryDate, GETDATE())>=30
);

I just want to delete the details in MemberDetails where the three conditions below follow:

1. Member is not active
2. Option B is N, and
3. The expiry date is overdue by more than 30 days.

When I am using the code above, the system is deleting everything in the MemberDetails table.

I can't figure why this is happening.

Any thoughts?

Answer 1

Exists is used for if the subquery is either true or false. since the subquery returns true, all the records on the table gets to be deleted. Try like below instead using Exists.

DELETE MemberDetails a
WHERE a.MemberID in (
    SELECT m.MemberID
    FROM MemberDetails m
    INNER JOIN PaymentDetails pd ON
    pd.MemberID = m.MemberID
    INNER JOIN GDPRCompliance gd ON
    gd.MemberID = m.MemberID
    WHERE MActive= 'N' AND OptionB= 'N' and DATEDIFF(dd,ExpiryDate, GETDATE())>=30
);