Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Delete with join -- deleting related table entries?
Date: Wed, 8 Feb 2006 10:18:46 -0800
Message-ID: <144D12D7DD4EC04F99241498BB4EEDCC20CC8E@nelson.osl.com>
Thread-Topic: [SQL] Delete with join -- deleting related table entries?
Thread-Index: AcYs2x6jd/MIsGnpQVq1JhrT1LcySwAAC9hg
From: "Owen Jacobson" <ojacobson@osl.com>
To: <pgsql-sql@postgresql.org>
Cc: "Markus Schaber" <schabi@logix-tt.com>

Bryce Nesbitt wrote:

> Markus Schaber wrote:
>
> > Bryce Nesbitt wrote:
> >
> >  =20
> >> 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=20
> >> was thinking that maybe SQL could do it all for me....
> >
> > Why do you think this won't work? (provided you add the=20
> missing ) and ; :-)
>=20
> Wow.  It worked.  Cool.  I guess the reservations don't get deleted
> until they are not needed any more...
>=20
> 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