From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | Michael Chaney <mdchaney(at)michaelchaney(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: correlated delete with 'in' and 'left outer join' |
Date: | 2004-02-27 17:05:48 |
Message-ID: | 403F78EC.4040507@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Michael Chaney wrote:
> Please, before continuing this thread, read my post below. What you're
> all getting around to, albeit painfully, is that this subquery is
> worthless as-is. This is the mysql way of finding rows in one table
> with no match in another without the convenience of the "in" or "exists"
> constructs.
>
> Because we're using Postgres and have those constructs, the original
> query can be rewritten simply with either:
>
> delete from LogEvent where EventType != 'i' and ItemID not in
> (select ItemID from Item)
>
> That's it. That's the whole query. It does what he wants.
One more minor point. :-)
If you are using 7.3 or earlier, PostgreSQL will sequentially scan
the IN subquery result, which executes quite slowly and therefore
the EXISTS method Stephan stated should be used:
DELETE FROM LogEvent
WHERE EventType != 'i' AND NOT EXISTS (
SELECT 1
FROM Item
WHERE Item.ItemID = LogEvent.ItemID
);
If you are using >= 7.4, then your query above is optimal:
http://www.postgresql.org/docs/7.4/static/release.html#RELEASE-7-4
Just something to consider,
Mike Mascari
>
> Michael
From | Date | Subject | |
---|---|---|---|
Next Message | dewins murillo | 2004-02-27 17:21:45 | Here there is a Postgresql for Windows Sample APP |
Previous Message | Michael Chaney | 2004-02-27 17:01:10 | Re: correlated delete with 'in' and 'left outer join' |