RE: SQL UPDATE not updating all rows

[MKirkpatrick@xxxxxxxxxxxxxxxxx]

Thanks, Elvis.

It was a sequence error.  Looking at the CLLE, I see that the TABLEC rows 
I want to update have not even been written.  This is done later in the 
pgm.

Thanks again for the extras set of eyes.




From:
"Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
To:
"'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Date:
11/13/2008 09:15 AM
Subject:
RE: SQL UPDATE not updating all rows
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



I see nothing wrong with your current scheme.
Take another look at the RUNSQLSTM source member... perhaps the UPDATE
statement isn't exactly what you think it is.
Or, perhaps this is a sequence error?  I mean, after the INSERT, data in
tableB isn't quite what you expected it to be.
BTW, any errors in the spooled file generated by RUNSQLSTM?

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: SQL UPDATE not updating all rows

I use RUNSQLSTM in a CLLE pgm (interactively) to process a source member 
that does the following:

-- insert rows into TABLEB  from TABLEA where they do not already exist in 

TABLEB
-- update TABLEC where the row exists in TABLEB  (note: all rows in TABLEB 

DO exist in TABLEC)

The first insert works fine.  However, the update is not updating all of 
the rows in TABLEC that it should.  If I run the same SQL stmt using 
interactive SQL, all rows in TABLEC are updated properly.  I use 
COMMIT(*NONE)  and CLOSQLCSR(*ENDMOD) on the RUNSQLSTM.  It seems that the 

rows inserted into TABLEB have not been committed before the update stmt 
processes them..

Is this because I use CLOSQLCSR(*ENDMOD)?  If so, how can I better handle 
this.  Do I need to break out the update of TABLEC into another RUNSQLSTM 
process?

Thanks.