Re: Delete with join -- deleting related table entries?

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