I'm somewhat new to DB2 on an AS400, today I managed to set the value of a single field to the wrong amount in 300k+ records, and now I need to fix it...
I'm having a problem with the UPDATE statement:
UPDATE WHSPSLP
SET WHSPSLP.WHS_TOT_VALUE = BUWHSPSLP.WHS_TOT_VALUE
WHERE WHSPSLP.WHS_PSLP_NO = BUWHSPSLP.WHS_PSLP_NO
I'm updating the field with the correct value from a back up of the table, but I just can't get it right.
Thanks
If BUWHSPSLP.WHS_TOT_VALUE is a constant value, for all the records you can use one update statement like this:
DECLARE v_WHSTotalValue INT;
SET v_WHSTotalValue = (
SELECT WHS_TOT_VALUE
FROM BUWHSPSLP
FETCH FIRST 1 ROWS ONLY
);
UPDATE WHSPSLP
SET WHS_TOT_VALUE = v_WHSTotalValue
WHERE WHS_PSLP_NO IN (
SELECT WHS_PSLP_NO
FROM BUWHSPSLP
);
Else, you will need to loop through all the records using a cursor and update each of them as follows:
BUWHSPSLPLoop:
FOR v AS cur1 CURSOR WITH HOLD FOR
SELECT WHS_TOT_VALUE, WHS_PSLP_NO
FROM BUWHSPSLP
DO
UPDATE WHSPSLPSET
SET WHS_TOT_VALUE = v.WHS_TOT_VALUE
WHERE WHS_PSLP_NO = v.WHS_PSLP_NO;
END FOR BUWHSPSLPLoop;
