Re: Using SQL to delete a record based on a non-matching value in another record

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Sorry missed a line...

delete
from marktest/loc_file a
where b.library='R37MODS'
  and exists ( select * from marktest/loc_file b
                    where a.pgm = b.pgm
                        and a.library <> b.library
                   )

HTH,
Charles

On Fri, Oct 1, 2010 at 4:10 PM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
> delete
> from marktest/loc_file a
> where a.library <> b.library and b.library='R37MODS'
>   and exists ( select * from marktest/loc_file b
>                     where a.pgm = b.pgm )
>
> HTH,
> Charles
>
>
>
> On Fri, Oct 1, 2010 at 3:47 PM, J M Plank <plank.computer@xxxxxxxxx> wrote:
> > I know I'm missing something simple here - hopefully someone can point out
> > what it is.
> >
> > I needed a quick and dirty way of listing programs that might need to be
> > modified.  We have a base code library and a modified code library.  I have
> > a physical file created from a CPYSPLF of a Hawkeye report.  I have a CL
> > with various SQL commands to clean up this physical file (header records,
> > etc.).  I need to weed out records with objects in the base code library
> > that are also in the modified code library.  This cleaned up file will go
> > into a spreadsheet to be passed around by managers.
> >
> > This statement gives me the 210 records I wish to remove from the file (data
> > sample at end):
> > select * from marktest/loc_file a join marktest/loc_file b on a.pgm = b.pgm
> > where a.library <> b.library and b.library='R37MODS'
> >
> > I thought wrongly that a statement similar to this would delete those
> > records:
> > delete from marktest/loc_file a join marktest/loc_file b on a.pgm = b.pgm
> > where a.library <> b.library and b.library='R37MODS'
> > What am I missing?  I suspect it is the JOIN that is giving me problems.
> > The message I'm getting is
> > Message . . . . :   Keyword JOIN not expected. Valid tokens: USE SKIP WAIT
> > WITH WHERE.
> > Cause . . . . . :   The keyword JOIN was not expected here.  A syntax error
> > was detected at keyword JOIN.  The partial list of valid tokens is USE SKIP
> >  WAIT WITH WHERE. This list assumes that the statement is correct up to the
> > unexpected keyword.  The error may be earlier in the statement but the
> >  syntax of the statement seems to be valid up to this point.
> >
> > Recovery  . . . :   Examine the SQL statement in the area of the specified
> > keyword.  A colon or SQL delimiter may be missing. SQL requires reserved
> >  words to be delimited when they are used as a name. Correct the SQL
> > statement and try the request again.
> >
> > Other information that might be useful.  I'm using RUNSQL both from a
> > command line and in an interactive CL, on a V7R1 machine.  I've checked the
> > archives, but haven't found a statement that is similar to what I'm trying
> > to do.
> >
> > A data sample:
> > PGM         LIBRARY     ATTRIBUTE
> > INUPCCCKCL  R37MODS     CLP
> > INENMOC2    R37MODS     CLLE
> > INENMOC2    R37OBJ      CLP      <== Delete, base code, won't be modified
> > (attribute doesn't matter)
> > APMAPC      R37OBJ      RPGLE
> > APMAVN      R37MODS     RPGLE
> > APMAVN      R37OBJ      RPGLE    <== Delete, base code, won't be modified
> > APRPAV2     R37MODS     RPGLE
> > ARETAD      R37OBJ      RPGLE
> > ARETCS      R37MODS     RPGLE
> > ARETCS      R37OBJ      RPGLE    <== Delete, base code, won't be modified
> > ARETSA      R37MODS     RPGLE
> > ARETSA      R37OBJ      RPGLE    <== Delete, base code, won't be modified
> > ARMABK      R37OBJ      RPGLE
> >
> > Thanks.
> > Mark Plank
> > --
> > 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.