RE: Using SQL to delete a record based on a non-matching value in anotherrecord

Mark,

Try:

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

using an exists test instead of a join.

Paul

Principal Programmer Analyst
IS Supply Chain/Replenishment

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of J M Plank
Sent: Friday, October 01, 2010 3:47 PM
To: Midrange Systems Technical Discussion
Subject: Using SQL to delete a record based on a non-matching value in anotherrecord

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