pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDelete with join -- deleting related table entries?
10+ messages / 4 participants
[nested] [flat]
* Delete with join -- deleting related table entries?
@ 2006-02-08 17:14 Bryce Nesbitt <bryce1@obviously.com>
2006-02-08 17:25 ` Re: Delete with join -- deleting related table entries? BigSmoke <bigsmoke@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Bryce Nesbitt @ 2006-02-08 17:14 UTC (permalink / raw)
To: pgsql-sql
When I delete a record from a certain table, I need to delete a
(possibly) attached note as well. How can I do this with postgres? The
tables are like this:
reservation
reservation_id
stuff...
isuse
issue_id
reservation_id
stuff..
note
issue_id
text comments...
A select that pulls out what I want to delete is:
SELECT reservation_id,issue_id,note_id,eg_note.comments FROM
eg_reservation
LEFT JOIN eg_issue USING (reservation_id)
LEFT JOIN eg_note USING (issue_id)
WHERE reservation_id > condition;
Can anyone help me turn this into a DELETE statement?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete with join -- deleting related table entries?
2006-02-08 17:14 Delete with join -- deleting related table entries? Bryce Nesbitt <bryce1@obviously.com>
@ 2006-02-08 17:25 ` BigSmoke <bigsmoke@gmail.com>
2006-02-08 17:33 ` Re: Delete with join -- deleting related table entries? Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 1 reply; 10+ messages in thread
From: BigSmoke @ 2006-02-08 17:25 UTC (permalink / raw)
To: pgsql-sql
I'm not sure if this is true for you as I can't see your complete table
definitions, but I'd usually do this by using
issue_id INTEGER REFERENCES issue ON DELETE CASCADE
in my column definition.
See [1] for more information.
[1]http://www.postgresql.org/docs/current/interactive/ddl-constraints.html#DDL-CONSTRAINTS-FK
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete with join -- deleting related table entries?
2006-02-08 17:14 Delete with join -- deleting related table entries? Bryce Nesbitt <bryce1@obviously.com>
2006-02-08 17:25 ` Re: Delete with join -- deleting related table entries? BigSmoke <bigsmoke@gmail.com>
@ 2006-02-08 17:33 ` Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 0 replies; 10+ messages in thread
From: Bryce Nesbitt @ 2006-02-08 17:33 UTC (permalink / raw)
To: pgsql-sql
BigSmoke wrote:
> ...I'd usually do this by using
> issue_id INTEGER REFERENCES issue ON DELETE CASCADE
>
Good, and valuable, thanks!
But at the moment I can't change the schema. So is there a way to do a
cascaded or joined delete
in a sql schema that did not anticipate it? Again, this is deleting
rows from three related tables, as a single atomic operation.
-Bryce
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete with join -- deleting related table entries?
@ 2006-02-08 17:32 Owen Jacobson <ojacobson@osl.com>
2006-02-08 17:41 ` Re: Delete with join -- deleting related table entries? Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 1 reply; 10+ messages in thread
From: Owen Jacobson @ 2006-02-08 17:32 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; pgsql-sql
Bryce Nesbitt wrote:
> When I delete a record from a certain table, I need to delete a
> (possibly) attached note as well. How can I do this with
> postgres? The
> tables are like this:
>
> reservation
> reservation_id
> stuff...
>
> isuse
> issue_id
> reservation_id
references reservation (reservation_id) -- ADD
> stuff..
>
> note
> issue_id
references isuse (issue_id) -- ADD (kept typo in example)
> text comments...
>
> A select that pulls out what I want to delete is:
>
> SELECT reservation_id,issue_id,note_id,eg_note.comments FROM
> eg_reservation
> LEFT JOIN eg_issue USING (reservation_id)
> LEFT JOIN eg_note USING (issue_id)
> WHERE reservation_id > condition;
>
> Can anyone help me turn this into a DELETE statement?
1. Add foreign key references between the tables to ensure that there are only notes and issues (isuses? :) for existing issues and reservations respectively. You can make those references 'ON DELETE CASCADE' so that a delete of the original reservation cascades down to related entries in the issue table, which in turn cascade down to the related entries in the note table.
2. Or...
BEGIN;
DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
WHERE reservation_id = reservation_to_delete);
DELETE FROM isuse WHERE reservation_id = reservation_to_delete;
DELETE FROM reservations WHERE reservation_id = reservation_to_delete;
END;
with an appropriate value or expression substituted into reservation_to_delete. This would be the "hard way", but (as it's in a single transaction) will still protect other clients from seeing a partial delete.
Get yourself a good, non-MySQL-specific database book, which should explain how referential integrity is handled in databases.
-Owen
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete with join -- deleting related table entries?
2006-02-08 17:32 Re: Delete with join -- deleting related table entries? Owen Jacobson <ojacobson@osl.com>
@ 2006-02-08 17:41 ` Bryce Nesbitt <bryce1@obviously.com>
2006-02-08 17:55 ` Re: Delete with join -- deleting related table entries? Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 10+ messages in thread
From: Bryce Nesbitt @ 2006-02-08 17:41 UTC (permalink / raw)
To: pgsql-sql
Owen Jacobson wrote:
> BEGIN;
> DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
> WHERE reservation_id = reservation_to_delete);
> DELETE FROM isuse WHERE reservation_id = reservation_to_delete;
> DELETE FROM reservations WHERE reservation_id = reservation_to_delete;
> COMMIT;
>
> With an appropriate value or expression substituted into reservation_to_delete. This would be the "hard way", but (as it's in a single transaction) will still protect other clients from seeing a partial delete.
Yup, that's exactly how I delete reservations one a time. But here I
need to select a few thousand reservations, and I don't think this will
work:
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....
-Bryce
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete with join -- deleting related table entries?
2006-02-08 17:32 Re: Delete with join -- deleting related table entries? Owen Jacobson <ojacobson@osl.com>
2006-02-08 17:41 ` Re: Delete with join -- deleting related table entries? Bryce Nesbitt <bryce1@obviously.com>
@ 2006-02-08 17:55 ` Markus Schaber <schabi@logix-tt.com>
2006-02-08 18:11 ` Re: Delete with join -- deleting related table entries? Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 1 reply; 10+ messages in thread
From: Markus Schaber @ 2006-02-08 17:55 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
Hi, Bryce,
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 ; :-)
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete with join -- deleting related table entries?
2006-02-08 17:32 Re: Delete with join -- deleting related table entries? Owen Jacobson <ojacobson@osl.com>
2006-02-08 17:41 ` Re: Delete with join -- deleting related table entries? Bryce Nesbitt <bryce1@obviously.com>
2006-02-08 17:55 ` Re: Delete with join -- deleting related table entries? Markus Schaber <schabi@logix-tt.com>
@ 2006-02-08 18:11 ` Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 0 replies; 10+ messages in thread
From: Bryce Nesbitt @ 2006-02-08 18:11 UTC (permalink / raw)
To: pgsql-sql; +Cc: Markus Schaber <schabi@logix-tt.com>
Markus Schaber wrote:
> Hi, Bryce,
>
> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete with join -- deleting related table entries?
@ 2006-02-08 17:35 Owen Jacobson <ojacobson@osl.com>
0 siblings, 0 replies; 10+ messages in thread
From: Owen Jacobson @ 2006-02-08 17:35 UTC (permalink / raw)
To: Owen Jacobson <ojacobson@osl.com>; Bryce Nesbitt <bryce1@obviously.com>; pgsql-sql
Owen Jacobson wrote:
> BEGIN;
> DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
> WHERE reservation_id = reservation_to_delete);
> DELETE FROM isuse WHERE reservation_id = reservation_to_delete;
> DELETE FROM reservations WHERE reservation_id =
> reservation_to_delete;
> END;
That should be COMMIT;, not END;. Been writing too much pl/pgsql.
-Owen
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete with join -- deleting related table entries?
@ 2006-02-08 17:56 Owen Jacobson <ojacobson@osl.com>
0 siblings, 0 replies; 10+ messages in thread
From: Owen Jacobson @ 2006-02-08 17:56 UTC (permalink / raw)
To: pgsql-sql
Bryce Nesbitt wrote:
> Owen Jacobson wrote:
>
> > BEGIN;
> > DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse
> > WHERE reservation_id = reservation_to_delete);
> > DELETE FROM isuse WHERE reservation_id = reservation_to_delete;
> > DELETE FROM reservations WHERE reservation_id =
> reservation_to_delete;
> > COMMIT;
> >
> > With an appropriate value or expression substituted into
> > reservation_to_delete. This would be the "hard way", but (as
> > it's in a single transaction) will still protect other
> > clients from seeing a partial delete.
>
> Yup, that's exactly how I delete reservations one a time. But here I
> need to select a few thousand reservations, and I don't think
> this will
> work:
> 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....
Further thinking produced the following functional example.
CREATE TABLE reservation (
reservation_id INTEGER NOT NULL,
date DATE NOT NULL
);
CREATE TABLE issue (
issue_id INTEGER NOT NULL,
reservation_id INTEGER NOT NULL
);
CREATE TABLE note (
issue_id INTEGER NOT NULL
);
INSERT INTO reservation VALUES (1, '2006-01-01');
INSERT INTO reservation VALUES (2, '2006-01-15');
INSERT INTO reservation VALUES (3, '2006-02-01');
INSERT INTO issue VALUES (1, 1);
INSERT INTO issue VALUES (2, 1);
INSERT INTO issue VALUES (3, 2);
INSERT INTO issue VALUES (4, 2);
INSERT INTO issue VALUES (5, 3);
INSERT INTO issue VALUES (6, 3);
INSERT INTO note VALUES (1);
INSERT INTO note VALUES (2);
INSERT INTO note VALUES (3);
INSERT INTO note VALUES (4);
INSERT INTO note VALUES (5);
INSERT INTO note VALUES (6);
-- PostgreSQL 8.0 and prior
BEGIN;
DELETE FROM note
WHERE note.issue_id = issue.issue_id
AND issue.reservation_id = reservation.reservation_id
AND reservation.date > '2006-01-16';
DELETE FROM issue
WHERE issue.reservation_id = reservation.reservation_id
AND reservation.date > '2006-01-16';
DELETE FROM reservation WHERE date > '2006-01-16';
COMMIT;
-- PostgreSQL 8.1 and later
BEGIN;
DELETE FROM note USING issue, reservation
WHERE note.issue_id = issue.issue_id
AND issue.reservation_id = reservation.reservation_id
AND reservation.date > '2006-01-16';
DELETE FROM issue USING reservation
WHERE issue.reservation_id = reservation.reservation_id
AND reservation.date > '2006-01-16';
DELETE FROM reservation WHERE date > '2006-01-16';
COMMIT;
The version using subselects works fine, too.
-Owen
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete with join -- deleting related table entries?
@ 2006-02-08 18:18 Owen Jacobson <ojacobson@osl.com>
0 siblings, 0 replies; 10+ messages in thread
From: Owen Jacobson @ 2006-02-08 18:18 UTC (permalink / raw)
To: pgsql-sql; +Cc: Markus Schaber <schabi@logix-tt.com>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-02-08 18:18 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-08 17:14 Delete with join -- deleting related table entries? Bryce Nesbitt <bryce1@obviously.com>
2006-02-08 17:25 ` BigSmoke <bigsmoke@gmail.com>
2006-02-08 17:33 ` Bryce Nesbitt <bryce1@obviously.com>
2006-02-08 17:32 Re: Delete with join -- deleting related table entries? Owen Jacobson <ojacobson@osl.com>
2006-02-08 17:41 ` Bryce Nesbitt <bryce1@obviously.com>
2006-02-08 17:55 ` Markus Schaber <schabi@logix-tt.com>
2006-02-08 18:11 ` Bryce Nesbitt <bryce1@obviously.com>
2006-02-08 17:35 Re: Delete with join -- deleting related table entries? Owen Jacobson <ojacobson@osl.com>
2006-02-08 17:56 Re: Delete with join -- deleting related table entries? Owen Jacobson <ojacobson@osl.com>
2006-02-08 18:18 Re: Delete with join -- deleting related table entries? Owen Jacobson <ojacobson@osl.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