Posted by metaperl on 01/23/08 18:57

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.

• Next in forum: Months Between
• Prev in forum: Problem migrating DTS to SSIS
• Thread view: DELETE ... NOT query does not delete all that I expected

[Reply to this message]