Re: SQL - Delete with multiple tables

["John Candidi" <jacandidi@xxxxxxxxxxxxxxxxxxxx>]

Tnahk you everybody for your replies..I used Billy's..Worked beautifully
----- Original Message ----- 
From: "Billy Rowe" <billyrowe@xxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Friday, March 25, 2005 3:51 PM
Subject: Re: SQL - Delete with multiple tables


> You could also use the "EXISTS" clause which sometimes is faster and
> since you are using the iSeries Navigator you can check both methods
> with Visual Explain and also test both methods by turning on debug for
> your iSeries SQL connection to check out the details:
>
> Delete from rugd80dat.rcd4cpp A
>   Where exists (
>    SELECT *
>     from rugd80dat.rcd3cpp
>    Where A.d4bznb   =  d3bznb
>     AND  A.d4bwrtdt =  d3bwrtdt
>     AND  d3polsym between '000' and '999'
>     AND  d3e3st = 'P'
>                );
>
> Start Debug via the menu "Options" or this statment:
> CALL QSYS.QCmdExc('STRDBG UPDPROD(*YES)',0000000020.00000);
>
>
> >> 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')
>
> > 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.