Re: SQL - Delete with multiple tables

[Billy Rowe <billyrowe@xxxxxxx>]

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.