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

On Jan 23, 10:57 am, metaperl <metap...@xxxxxxxxx> 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
.