Skip site navigation (1) Skip section navigation (2)

Re: correlated delete with 'in' and 'left outer join'

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 (view raw or flat)
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



In response to

Responses

pgsql-general by date

Next:From: dewins murilloDate: 2004-02-27 17:21:45
Subject: Here there is a Postgresql for Windows Sample APP
Previous:From: Michael ChaneyDate: 2004-02-27 17:01:10
Subject: Re: correlated delete with 'in' and 'left outer join'

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group