Обсуждение: 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,

You may want to use the USING clause:

DELETE FROM events USING event_types
  WHERE events.event_type_fk = event_types.event_type.id
  AND relevance_level IN ( 1, 3, 5, 7)
  AND <ANY_ANOTHER_CONDITION_YOU_

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