Re: Delete with subquery deleting all records

Your top-posting was confusing to me, but I eventually figured out what went
where.

Francisco Reyes wrote:
> However.. I find it very strange that just the selects by themselves
> produced the same ouput up to limit 100.

Strange? Why? Did you expect a particular statistical distribution? Perhaps
you were surprised by the extent of the situation, not thinking there could be
100 records that didn't match?

Apparently by coincidence the first 100 distinct values returned from
export_messages just happened not to have corresponding rows in exports.

There is really nothing strange about your result, just like there's really
nothing strange about getting 100 heads in a row in a coin toss.

> SELECT distinct export_messages.export_id as id
> FROM export_messages
> LEFT OUTER JOIN exports ON (export_messages.export_id = exports.export_id)
> limit 100;
>
> and
> SELECT distinct export_messages.export_id as id
> FROM export_messages
> LEFT OUTER JOIN exports ON
> (export_messages.export_id = exports.export_id)
> WHERE exports.export_id IS NULL limit 100;
>
> Produced the same output.

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.

As Alban said, a better query would be (SELECT column changed from his
suggestion):

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

or

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

--
Lew