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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
