Reply-To: <michael.mattox@verideon.com>
From: "Michael Mattox" <michael.mattox@verideon.com>
To: <pgsql-sql@postgresql.org>
Subject: How to delete multiple rows from multiple joined tables
Date: Mon, 26 May 2003 15:48:16 +0200
Message-ID: <CJEBLDCHAADCLAGIGCOOAEMHCHAA.michael.mattox@verideon.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Importance: Normal

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