Re: SQL - Delete with multiple tables

SELECT * FROM ROB/MASTERF
BZNB  BWRTDT      POLSYM  E3ST
 A    2005-03-12   000     P 
 A    2005-03-12   aaa     P 
 A    2005-03-12   000     Z 
 A    2005-05-12   000     P 

SELECT * FROM ROB/PURGEF
....+....1....+.
BZNB  BWRTDT 
 A    2005-03-12

SELECT * FROM ROB/MASTERF 
         JOIN ROB/PURGEF 
   ON MASTERF.BZNB = PURGEF.BZNB 
  AND MASTERF.BWRTDT = PURGEF.BWRTDT 
WHERE MASTERF.POLSYM BETWEEN '000' AND '999'
  AND MASTERF.E3ST = 'P' 
....+....1....+....2....+....3....+....4....+...
BZNB  BWRTDT      POLSYM  E3ST  BZNB  BWRTDT 
 A    2005-03-12   000     P     A    2005-03-12

DELETE FROM ROB/MASTERF 
WHERE (BZNB CONCAT CHAR(BWRTDT)) IN ( 
SELECT MASTERF.BZNB CONCAT CHAR(MASTERF.BWRTDT)
  FROM ROB/MASTERF 
  JOIN ROB/PURGEF 
   ON MASTERF.BZNB = PURGEF.BZNB 
  AND MASTERF.BWRTDT = PURGEF.BWRTDT) 
  AND MASTERF.POLSYM BETWEEN '000' AND '999' 
  AND MASTERF.E3ST = 'P' 
1 rows deleted from MASTERF in ROB. 

SELECT * FROM ROB/MASTERF 
....+....1....+....2....+....3 
BZNB  BWRTDT      POLSYM  E3ST 
 A    2005-03-12   aaa     P 
 A    2005-03-12   000     Z 
 A    2005-05-12   000     P 
********  End of data  ********


Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"John Candidi" <jacandidi@xxxxxxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
03/24/2005 10:19 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
<midrange-l@xxxxxxxxxxxx>
cc

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.