Обсуждение: Delete with join -- deleting related table entries?
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?
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
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 existingissues and reservations respectively. You can make those references 'ON DELETE CASCADE' so that a delete of theoriginal reservation cascades down to related entries in the issue table, which in turn cascade down to the related entriesin the note table. 2. Or... BEGIN; DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse WHERE reservation_id = reservation_to_delete); DELETEFROM 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'sin 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
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
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
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
(asit'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_idfrom reservations where date > magic); DELETE FROM isuse WHERE reservation_id IN (select
reservation_idfrom reservations where date > magic) DELETE FROM reservations WHERE reservation_id IN (select
reservation_idfrom 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
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
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'; DELETEFROM reservation WHERE date > '2006-01-16'; COMMIT; The version using subselects works fine, too. -Owen
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.
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 entriesin 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 andbe done with it. :) -Owen