From: Andreas <maps.on@gmx.net>
To: pgsql-sql@postgresql.org
Subject: howto delete using a join ?
Date: Sat, 24 Jul 2010 06:05:57 +0200
Message-ID: <4C4A66A5.9070609@gmx.net> (raw)
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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maps.on@gmx.net
Subject: Re: howto delete using a join ?
In-Reply-To: <4C4A66A5.9070609@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox