pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhowto delete using a join ?
3+ messages / 3 participants
[nested] [flat]
* howto delete using a join ?
@ 2010-07-24 04:05 Andreas <maps.on@gmx.net>
2010-07-24 09:22 ` Re: howto delete using a join ? Dmitriy Igrishin <dmitigr@gmail.com>
2010-07-24 11:47 ` Re: howto delete using a join ? Joshua Tolley <eggyknap@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Andreas @ 2010-07-24 04:05 UTC (permalink / raw)
To: 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);
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: howto delete using a join ?
2010-07-24 04:05 howto delete using a join ? Andreas <maps.on@gmx.net>
@ 2010-07-24 09:22 ` Dmitriy Igrishin <dmitigr@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Dmitriy Igrishin @ 2010-07-24 09:22 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
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_
NEED> ;
Regards,
Dmitriy
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: howto delete using a join ?
2010-07-24 04:05 howto delete using a join ? Andreas <maps.on@gmx.net>
@ 2010-07-24 11:47 ` Joshua Tolley <eggyknap@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Joshua Tolley @ 2010-07-24 11:47 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
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
Attachments:
[application/pgp-signature] signature.asc (196B, ../../4c4ad2d6.0bb48c0a.7970.5678@mx.google.com/2-signature.asc)
download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-07-24 11:47 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-24 04:05 howto delete using a join ? Andreas <maps.on@gmx.net>
2010-07-24 09:22 ` Re: howto delete using a join ? Dmitriy Igrishin <dmitigr@gmail.com>
2010-07-24 11:47 ` Joshua Tolley <eggyknap@gmail.com>
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