RE: SQL - Delete with multiple tables

["Himes, Jay" <jehimes@xxxxxxxxxxx>]

This will potentially be slow depending on how many records are in the
sub-select.

delete
FROM rugd80dat.rcd4cpp 
Where <keyfield1 from rcd4cpp> || <keyfield2 from rcd4cpp> || <keyfield3
from rcd4cpp>
In (
SELECT <keyfield1 from rcd4cpp> || <keyfield2 from rcd4cpp> ||
<keyfield3 from rcd4cpp>
 FROM rugd80dat.rcd4cpp AS rcd4, rugd80dat.rcd3cpp AS rcd3 
 WHERE rcd4.d4bznb = rcd3.d3bznb AND rcd4.d4bwrtdt = rcd3.d3bwrtdt AND 
    (rcd3.d3polsym BETWEEN '000' AND '999') AND rcd3.d3e3st = 'P'
)





-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John Candidi
Sent: Thursday, March 24, 2005 10:20 AM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL - Delete with multiple tables

I have the folowing SQL query:

SELECT rcd4.* FROM rugd80dat.rcd4cpp AS rcd4, rugd80dat.rcd3cpp AS rcd3
WHERE rcd4.d4bznb = rcd3.d3bznb AND 
    rcd4.d4bwrtdt = rcd3.d3bwrtdt AND 
    (rcd3.d3polsym BETWEEN '000' AND '999') AND 
    rcd3.d3e3st = 'P'

I want to change this to be able to delete the result set, but having
problems with the SQL not wanting to run a delete with multiple tables
in the list.

I am using the iSeries Navigator RUN SQL dialog to try to do this.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.