RE: SQL update make fields match

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Jeff Crosby
> Sent: Wednesday, June 22, 2005 5:02 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL update make fields match
> 
> 
> > 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.

I do.  It should work fine.  Call IBM.


> 
> 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?

Exactly.

You're limiting the rows in DMITMMST that you are looking at.  At the same 
time, you are updating all the rows in DMCHNBIL.  When an ITNBR exists in 
DMCHNBIL but the DMITMMST record is something besides a 'D', the results of the 
subselect is NULL.  Since DMCHNBIL.ACREC is not NULL capable, you get the 
SQL0407 message.

If DMCHNBIL.ACREC was NULL capable, the statement would run, but the statement 
wouldn't do what you want.  You'd end up with every record in DMCHNBIL having 
ACREC = 'D' or NULL.

Again, what you need is a WHERE clause on the UPDATE statement itself.  You 
could use the same form as the statement from before that is giving you trouble.

UPDATE DMCHNBIL                                              
  SET DMCHNBIL.ACREC = (SELECT DMITMMST.ACREC
                       FROM DMITMMST
                       WHERE DMITMMST.ITNBR = DMCHNBIL.ITNBR
                       )
  WHERE DMCHNBIL.ITNBR IN (SELECT ITNBR
                           FROM DMITMMST
                           WHERE DMITMMST.ACREC = 'D'
                           );

However, you don't really need to be this complex.  Since you're only updating 
DMCHNBIL items where the item number has a 'D' in DMITMMST.ACREC, you know that 
you'll always be setting DMCHNBIL.ACREC = 'D'.

Thus, the following would work:
UPDATE DMCHNBIL                                              
  SET DMCHNBIL.ACREC = 'D'
  WHERE DMCHNBIL.ITNBR IN (SELECT DMITMMST.ITNBR
                           FROM DMITMMST
                           WHERE DMITMMST.ACREC = 'D'
                           );

One final tip, mainly because I'm lazy and getting tired of typing these table 
names over and over ;-)

Remember the correlation name we discussed?  You can use that instead of typing 
the file names over and over.
UPDATE DMCHNBIL A                                             
  SET A.ACREC = 'D'
  WHERE A.ITNBR IN (SELECT B.ITNBR
                     FROM DMITMMST B
                     WHERE B.ACREC = 'D'
                     );

Actually, in the above statement, you don't need to qualify the column names at 
all; since the column name as used are unambiguous.
UPDATE DMCHNBIL
  SET ACREC = 'D'
  WHERE ITNBR IN (SELECT ITNBR
                   FROM DMITMMST 
                   WHERE ACREC = 'D'
                   );

In the original form, you do need some qualification:
UPDATE DMCHNBIL
  SET ACREC = (SELECT ACREC
                FROM DMITMMST
                WHERE DMITMMST.ITNBR = DMCHNBIL.ITNBR  <-- The only place where 
you have to have a qualified name.
                )
  WHERE ITNBR IN (SELECT ITNBR
                   FROM DMITMMST 
                   WHERE ACREC = 'D'
                   );

Again, you could use a correlation name to qualify the columns instead of the 
full table name:
UPDATE DMCHNBIL A
  SET ACREC = (SELECT ACREC
                FROM DMITMMST B
                WHERE B.ITNBR = A.ITNBR
                )
  WHERE ITNBR IN (SELECT ITNBR
                   FROM DMITMMST 
                   WHERE ACREC = 'D'
                   );


HTH,



Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121