From: Owen Jacobson <ojacobson@osl.com>
To: pgsql-sql@postgresql.org
Cc: Markus Schaber <schabi@logix-tt.com>
Subject: Re: Delete with join -- deleting related table entries?
Date: Wed, 8 Feb 2006 10:18:46 -0800
Message-ID: <144D12D7DD4EC04F99241498BB4EEDCC20CC8E@nelson.osl.com> (raw)
Bryce Nesbitt wrote:
> Markus Schaber wrote:
>
> > Bryce Nesbitt wrote:
> >
> >
> >> BEGIN;
> >> DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
> >> WHERE reservation_id IN
> >> (select reservation_id from reservations where date > magic)
> >> );
> >> DELETE FROM isuse WHERE reservation_id IN
> >> (select reservation_id from reservations where date > magic);
> >> DELETE FROM reservations WHERE reservation_id IN
> >> (select reservation_id from reservations where date > magic);
> >> COMMIT;
> >>
> >> I suppose I can do the subselect as a perl wrapper, but I
> >> was thinking that maybe SQL could do it all for me....
> >
> > Why do you think this won't work? (provided you add the
> missing ) and ; :-)
>
> Wow. It worked. Cool. I guess the reservations don't get deleted
> until they are not needed any more...
>
> Not the fastest thing in the world. But it worked.
EXPLAIN works with DELETE too. Joins in general on unindexed fields can be pretty slow; if you see a lot of Seq Scan entries in the EXPLAIN output, you might consider having indexes added on appropriate fields.
Then again, if you were going to do that, you might as well just fix the schema to use REFERENCES...ON DELETE CASCADE and be done with it. :)
-Owen
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: ojacobson@osl.com, schabi@logix-tt.com
Subject: Re: Delete with join -- deleting related table entries?
In-Reply-To: <144D12D7DD4EC04F99241498BB4EEDCC20CC8E@nelson.osl.com>
* 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