howto delete using a join ?

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: howto delete using a join ?
Date: 2010-07-24 04:05:57
Message-ID: 4C4A66A5.9070609@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Dmitriy Igrishin 2010-07-24 09:22:32 Re: howto delete using a join ?
Previous Message Torsten Zühlsdorff 2010-07-23 12:53:11 [TRIGGER] Returning values from BEFORE UPDATE trigger, without using them