Re: Delete with subquery deleting all records

Lew writes:

> Strange?  Why?  Did you expect a particular statistical distribution?  Perhaps


The impression was that one query was returning everything.. and the other
only the records that did not exist in the one table.

> you were surprised by the extent of the situation, not thinking there could be
> 100 records that didn't match?

Surprised that the outer join actually did ONLY display records that did not
exist in the second table, even though I did not have a where clause to not
list the records with a NULL value.

> The unconstrained outer join is guaranteed to return every distinct value of
> export_messages.export_id, the LEFT table, by the definition of LEFT OUTER JOIN.

That is what I expected, BUT it only returned records that did NOT exist in
the second table. It did not, as far as I could check, return all records.

>
> DELETE FROM export_messages WHERE NOT EXISTS
>   ( SELECT export_id FROM exports
>     WHERE exports.export_id = export_messages.export_id
>   );

That is what I ended up using.
It worked.


> DELETE FROM export_messages WHERE export_id NOT IN
>   ( SELECT export_id FROM exports );

Will keep that one handy too for future reference.

I think I may have not explained properly what I think I was seeing..
The left outter join without the where clause seemed to return the right
data.. only rows that existed in one table, but not in the other.

I looked at a few hundred records and check a good deal of them manually.

The issue was that I used it as a subquery to delete it seemed to produce
the entire list (ie all records from both tables)..

It is possible, or even likely, that I did something wrong or had some form
of oversight.. After I got the suggestion with the better query (with the
where clause) I did not go back to test anymore.

Thanks for your feedback.