Re: correlated delete with "in" and "left outer join" - Mailing list pgsql-general

Mike Mascari wrote:

> Stephan Szabo wrote:
>
>> On Thu, 26 Feb 2004 mike@linkify.com wrote:
>>
>>> I'm using postgresl 7.3.2 and have a query that executes very slowly.
>>>
>>> There are 2 tables: Item and LogEvent.  ItemID (an int4) is the
>>> primary key
>>> of Item, and is also a field in LogEvent.  Some ItemIDs in LogEvent
>>> do not
>>> correspond to ItemIDs in Item, and periodically we need to purge the
>>> non-matching ItemIDs from LogEvent.
>>
>> Perhaps
>> delete from LogEvent where EventType != 'i' and not exists
>>  (select * from Item i where i.ItemID=LogEvent.ItemID);
>
>
> Maybe I'm not reading his subquery correctly, but the left outer join
> will produce a row from LogEvent regardless of whether or not a matching
> row exists in Item, correct? So doesn't it reduce to:
>
> DELETE FROM LogEvent WHERE EventType <> 'i';

I failed to read what he was trying to accomplish and assumed the
original query was precisely what he intended. My apologies...

Mike Mascari