>I realize that the LEFT OUTER JOIN will grab all of the records from the ClientAddressHistory table but I have included a WHERE clause that limits the list to those with a NULL value in the Enrollment table (verified by the initial SELECT statement that only returns a subset of the result set). Despite this, all records are being deleted from the table.
>
>If I run just the select subquey contained in the delete transaction in retuns 0 rows. If I then change the DELETE statement to a SELECT statement and run the entire query it select 3 rows. So what i can't figure out is why it is deleting all rows based on a ClientID IN statement when the subquery returns only a subset of the ClientID values.
Sorry, it's an old thread, but you should have used INNER JOIN for your delete statement and then it would have worked. I'm wondering if there is any valid reason to ever use LEFT JOIN while doing a DELETE.
If it's not broken, fix it until it is.
My Blog