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

Originally I had a temporary table with about a million rows. Now I
have some questions about the row counts returned from some queries:

SELECT * FROM #details WHERE originator = 'J' and beneficiary = 'V';
-- the above yields 17 rows


DELETE FROM #details WHERE NOT(originator = 'J' AND beneficiary =
'V');
-- theoretically, should only leave 17 rows in table


SELECT * FROM #details ORDER BY aml_rec_id;
-- unfortunately, yields more than 300,00 rows... but only 17 should
be there


I am pretty sure if my delete had been this:
DELETE FROM #details WHERE id NOT IN
(SELECT id FROM #details WHERE originator = 'J' and beneficiary =
'V')

that it would have deleted what I wanted, but I dont understand why my
original DELETE statement failed to do what I wanted.

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

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

On Jan 23, 10:57 am, metaperl wrote:
> Originally I had a temporary table with about a million rows. Now I
> have some questions about the row counts returned from some queries:
>
> SELECT * FROM #details WHERE originator = 'J' and beneficiary = 'V';
> -- the above yields 17 rows
>
> DELETE FROM #details WHERE NOT(originator = 'J' AND beneficiary =
> 'V');
> -- theoretically, should only leave 17 rows in table
>
> SELECT * FROM #details ORDER BY aml_rec_id;
> -- unfortunately, yields more than 300,00 rows... but only 17 should
> be there
>
> I am pretty sure if my delete had been this:
> DELETE FROM #details WHERE id NOT IN
> (SELECT id FROM #details WHERE originator = 'J' and beneficiary =
> 'V')
>
> that it would have deleted what I wanted, but I dont understand why my
> original DELETE statement failed to do what I wanted.


Not to in any way detract from Plamen's edifying answer, but it
sounded like you wanted to delete the rows that had double-NULLs as
well, so perhaps you'd need

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

--Jeff