Message-ID: <4C4A66A5.9070609@gmx.net>
Date: Sat, 24 Jul 2010 06:05:57 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.9.2.7) Gecko/20100713 Thunderbird/3.1.1
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: howto  delete  using a join ?
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

  Hi,

is there a way to delete from a table using information from another 
table to decide if a row should be dropped?

In my case there is a log
events  ( event_id, event_type_fk, ...);
event_types ( event_type_id, relevance_level );

Now I'd like to delete all old events with certain relevance_levels but 
not all!

This works:
delete from events where event_id in
(
     select event_id from events join event_types on event_type_id = 
event_type_fk
     where relevance_level in ( 1, 3, 5, 7) and create_ts < '2010/01/01'
);

The following doesn't work but is there a more direct way which doesn't 
involve a subselect?

delete from events join event_types on event_type_id = event_type_fk 
where relevance_level in ( 1, 3, 5, 7);