Re: DELETE ... NOT query does not delete all that I expected

You are experiencing this because one or both columns 'originator' and 'beneficiary' have NULL values. Boolean logic with NULLs goes like this:

NOT TRUE -> FALSE
NOT FALSE -> TRUE
NOT UNKNOWN -> UNKNOWN

Since your WHERE filter evaluates to UNKNOWN when 'originator' and 'beneficiary' has NULL value, the DELETE skips those rows.

To delete the correct rows you can write the query like below:

DELETE FROM #details
WHERE NOT ((originator = 'J' AND originator IS NOT NULL)
AND (beneficiary = 'V' AND beneficiary IS NOT NULL))

HTH,

Plamen Ratchev
http://www.SQLStudio.com

.