Updating only a part of the result set

Mohammad Khan

Updating only a part of the result set
Problem : I have set a of records in which a decimal(5,2) column needs to
incremented / decremented so that the total is round figure. The difference
from the desired value in minimum increments (0.01) is less than the number
of records i.e. difference being 0.03 in a five record set. It does not
matter which three get updated. Normally one would process it through a
cursor updating records until the difference is made up.
New solution : I've an UDF by name SEQ which returns next integer starting
with 1 ( I posted it's code on the list a few months ago ). I thought about
using this function to limit the updates and save me some coding. Here is
the result from SPUFI test. The data doesn't need to be adjusted in this
case but it illustrates the point.

---------+---------+---------+---------+---------+---------+-
SELECT OWN_PART_NO
,OWN_TIS_CD
,OWN_P_W_IND
,OWN_PR_SHARE
FROM PARISP.TCWR_OWN_OWNER
WHERE OWN_TITLE_NO = 6220268 ;
---------+---------+---------+---------+---------+---------+-
OWN_PART_NO OWN_TIS_CD OWN_P_W_IND OWN_PR_SHARE
---------+---------+---------+---------+---------+---------+-
46775 2136 W 50.00
232047 2136 P 25.00
297358 2136 W 50.00
537551 2136 P 25.00
617080 2136 P 25.00
858403 2136 P 25.00
DSNE610I NUMBER OF ROWS DISPLAYED IS 6
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+-

UPDATE PARISP.TCWR_OWN_OWNER
SET OWN_PR_SHARE = OWN_PR_SHARE + 0.01
WHERE OWN_TITLE_NO = 6220268
AND PARISP.SEQ() <= 4 ;
---------+---------+---------+---------+---------+---------+-
DSNE615I NUMBER OF ROWS AFFECTED IS 4
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+-

SELECT OWN_PART_NO
,OWN_TIS_CD
,OWN_P_W_IND
,OWN_PR_SHARE
FROM PARISP.TCWR_OWN_OWNER
WHERE OWN_TITLE_NO = 6220268 ;
---------+---------+---------+---------+---------+---------+-
OWN_PART_NO OWN_TIS_CD OWN_P_W_IND OWN_PR_SHARE
---------+---------+---------+---------+---------+---------+-
46775 2136 W 50.01
232047 2136 P 25.01
297358 2136 W 50.01
537551 2136 P 25.01
617080 2136 P 25.00
858403 2136 P 25.00
DSNE610I NUMBER OF ROWS DISPLAYED IS 6
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+-

This seems to be working as desired. Does anyone see any pitfalls in this
approach ?
Thanks in advance
Mohammad