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.

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

On Jan 23, 10:57 am, metaperl <metap... (AT) gmail (DOT) com> wrote:

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