Обсуждение: howto delete using a join ?

  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);

Hey Andreas,<br /><br />You may want to use the USING clause:<br /><br />DELETE FROM events USING event_types<br /> 
WHEREevents.event_type_fk = <a href="http://event_types.event_type.id/"
target="_blank">event_types.event_type.id</a><br/>   AND relevance_level IN ( 1, 3, 5, 7)<br />   AND
<ANY_ANOTHER_CONDITION_YOU_<divclass="ii gt" id=":1s8">NEED> ;<br /><br />Regards,<br />Dmitriy<br /></div><br /> 

On Sat, Jul 24, 2010 at 06:05:57AM +0200, Andreas wrote:
>  Hi,
>
> is there a way to delete from a table using information from another
> table to decide if a row should be dropped?

Yes. See DELETE ... USING

http://www.postgresql.org/docs/8.4/interactive/sql-delete.html

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com