Update Query with Join in DB2/AS400

Question

Hi, 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

Answer 1

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;