|
|
Posted by jefftyzzer on 01/24/08 02:16
On Jan 23, 10:57 am, metaperl <metap...@gmail.com> 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
Navigation:
[Reply to this message]
|