Update Statement in Loop

General
Enthusiast

Update Statement in Loop

Hi Forum,

I'm looking for a method to execute a Stored Procedure where I can Loop through Month/Year values each loop. I have:

 

  • A table of MonYr values from 2007-2023.
  • A table with a PK of Row_ID, MonYr and an Amount field
  • A table to Update with a Row_ID values in the first Column and MonYr values across Row 1 (from 2007-2023)

I can get an Update statement to populate one month at a time, but I'd like to change the month each Update Loop & add the Amount for the relevant Row_ID & MonYr.

I am only going to try this for a small number of records as I've been advised of the impact of Looping in SPs.

Can anyone assist here?

Thanks Peter  

  

2 REPLIES
Enthusiast

Re: Update Statement in Loop

Here's my attempt at an UPDATE statement with a Macro in a Loop;

I'm not sure how to get the MonYr to change each Loop.

Thanks

 

Replace PROCEDURE dbc.SP_LOOP_TEST (
        IN i INTEGER,
        IN j INTEGER)

BEGIN
    DECLARE ii integer;
    set ii=i;
    while (ii<j) do
    begin
        set ii=ii+1;
        
 UPDATE dbc.AA_ROWID_MONYR_TRNSPSD (Has a Column ("A") of Row IDs and MonYr Values (200701-202312) from "B1" across Row 1. 
FROM  AA_TRANSPOSE_TEST A (Contains 3 Columns - 1.Matching Row_IDs from the above table, 2. MonYr Values (200701-202312), & 3. an Amount value per Row

INNER JOIN dbc.DATE_DIM B (Contains just MonYr values (200701-202312)  
ON B.Clndr_Month_Id = A.MonYr

LEFT JOIN dbc.AA_ROWID_MONYR_TRNSPSD C
ON A.Clndr_Month_Id = C.Variable_MonYr -- This (C.Variable_MonYr) value should change each Loop from 200701 to 200702 ... etc

SET C.Variable_MonYr = A.Val_Pl
WHERE A.Row_ID = C.Row_ID
AND B.MonYr = C.Variable_MonYr -- This (C.Variable_MonYr) value should change each Loop from 200701 to 200702 ... etc
;  
    
    end;
    end while;
END;

 

Enthusiast

Re: Update Statement in Loop

I've got a working version for one UPDATE below, I'd welcome any suggestions on how to Loop through this UPDATE changing the MonYr value each time.

 

BEGIN
    DECLARE ii integer;
    set ii=i;
    while (ii<j) do
    begin
        set ii=ii+1;
        
UPDATE dbc.AA_ROWID_MONYR_TRNSPSD (Column 'A' has Row_IDs and the Range 'B1 - ZZ1' has MonYr values in this format (_200701, _200702 ... ) as they are Column Field Names 
FROM  
(
SELECT A.Row_ID, A.MonYr, A.Val_Pl
FROM dbc.AA_TRANSPOSE_TEST A (Contains 3 Columns, 1. Row_IDs matching the above table, 2. MonYr values, 3. An Amount field) 
LEFT JOIN dbc.AA_ROWID_MONYR_TRNSPSD B
ON A.Row_ID = B.Row_ID 
) AS S
SET _200701 = S.Val_Pl  -- I need to change '_200701' to '_200702' etc each loop as this will change to the next Column.
WHERE Row_ID = S.Row_ID
AND 200701 = S.MonYr -- I also need to change '200701' to '200702' etc here each loop as this is the Amount to enter into the correct Row_ID & MonYr
;  
    
    end;
    end while;
END;