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

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.