RE: SQL update make fields match

["Jeff Crosby" <jlcrosby@xxxxxxxxxxxxxxxx>]

> I'd open a PMR on that baby.  Found a v4r5 PTF that mentions 
> a problem with NOT LIKE.  But nothing else.
> 
> Sounds like a bug to me, not a syntax problem.
> 
> What version of OS/400 are you on and do you have the latest 
> cume and DB group PTFs loaded?

V5R3 up to date on PTF's
 
> Can you try it in STRSQL?
> 
> What about "Run SQL Scripts" in iSeries Navigator?

No work either place.  I'm leaving it the way it is because I simply don't
know enough at this point.  I'll come back to it later.

Here's one I don't get though.  We have a file with special pricing records
(DMCHNBIL) and an item master (DMITMMST).

This script runs fine:

DELETE FROM DMCHNBIL WHERE ITNBR NOT IN (SELECT ITNBR FROM DMITMMST);
UPDATE DMCHNBIL                                              
  SET DMCHNBIL.ACREC = (SELECT DMITMMST.ACREC
                       FROM DMITMMST
                       WHERE DMITMMST.ITNBR = DMCHNBIL.ITNBR
                       );

The above will set DMCHNBIL.ACREC to match DMITMMST.ACREC.  Here's the
kicker though:  I want to mark the pricing record for deletion _only_ if the
item master record is marked for deletion.  But I do _not_ want to activate
any pricing records just because the item master is active.  So I added a
line:

DELETE FROM DMCHNBIL WHERE ITNBR NOT IN (SELECT ITNBR FROM DMITMMST);
UPDATE DMCHNBIL                                              
  SET DMCHNBIL.ACREC = (SELECT DMITMMST.ACREC
                       FROM DMITMMST
                       WHERE DMITMMST.ITNBR = DMCHNBIL.ITNBR
  Added this line -->  AND DMITMMST.ACREC = 'D'
                       );

This script will _not_ run.  I get the following:

SQL0100   0       5  Position 1 Row not found for DELETE.
SQL0088   0       9  Position 1 UPDATE applies to entire table.
SQL0407  30       6  Position 1 Null values not allowed in column or
variable ACREC.

I don't understand what this is telling me.  Since I just did a DELETE of
unmatching and these files are in a test library that only I have access to,
how can there be a mismatch on the key?  Oh wait.  Is this telling me there
is no match for any DMCHNBIL record where the DMITMMST record has something
other than a 'D' in ACREC?  Is this what you were trying to explain earlier?