pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to delete multiple rows from multiple joined tables
2+ messages / 2 participants
[nested] [flat]
* How to delete multiple rows from multiple joined tables
@ 2003-05-26 13:48 Michael Mattox <michael.mattox@verideon.com>
0 siblings, 1 reply; 2+ messages in thread
From: Michael Mattox @ 2003-05-26 13:48 UTC (permalink / raw)
To: pgsql-sql
I'm trying to use SQL to delete multiple rows from multiple tables that are
joined together. From what I've seen delete can only delete from a single
table, which is OK because I can just do separate deletes for each table.
But the problem is how do I specify which rows to delete? For example:
Table A is joined to Table B
Table B is joined to Table C
I want to delete all rows in table C & B that correspond to a row in Table
A. Here's my exact SQL which actually deletes all the rows:
delete from monitorstatusitemx where monitorx.namex='STRESS_TEST' and
monitorstatusx.jdoidx = monitorstatus_statusitemsx.jdoidx and
monitorstatus_statusitemsx.statusitemsx =
monitorstatusitemlistd8ea58a5x.jdoidx and
monitorstatusitemlistd8ea58a5x.statusitemlistx = monitorstatusitemx.jdoidx
(It's a little ugly because I'm using JDO which generates the schema.
Unfortunately deletes with JDO are very slow so I need to use SQL for this.)
Michael
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How to delete multiple rows from multiple joined tables
@ 2003-05-26 16:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Mattox <michael.mattox@verideon.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2003-05-26 16:55 UTC (permalink / raw)
To: michael.mattox@verideon.com; +Cc: pgsql-sql
"Michael Mattox" <michael.mattox@verideon.com> writes:
> I want to delete all rows in table C & B that correspond to a row in Table
> A.
Depending on how automatic you want that to be, perhaps foreign keys
would do what you want? You could set up B's and C's links to A as
"on delete cascade" foreign keys, and then a deletion from A would
clean them up too.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-05-26 16:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-26 13:48 How to delete multiple rows from multiple joined tables Michael Mattox <michael.mattox@verideon.com>
2003-05-26 16:55 ` Tom Lane <tgl@sss.pgh.pa.us>
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