I need to decide which field to update based on a looping variable. so replacing 'PERIOD||v_weeks' with something that will say something like PERIOD1, PERIOD2...PERIOD100
declare
v_itemid varchar2(6) := '111111';
v_weeks integer;
begin
FOR v_weeks IN 1..106
loop
select sum(qty)
into v_qty
from table_one
where item = v_itemid
and startdate = (v_startdate + (v_weeks * 7))
update table_two
set PERIOD||v_weeks = v_qty
where item = v_itemid
end loop;
end;
When a column or table name varies, you need to use dynamic SQL, which allows you to construct your SQL statement as a string value then ask Oracle to execute it. Before Oracle 8 this required use of the DBMS_SQL package (which can still be useful in some cases, such as to reduce the number of parse calls for performance reasons); but the more user-friendly method now available is to use EXECUTE IMMEDIATE:
EXECUTE IMMEDIATE 'UPDATE table_two SET PERIOD' || v_weeks || ' = :qty WHERE item = :item'
USING v_qty, v_itemid;
(I will suggest that having a separate column for each week is a questionable design. If you normalized this into a child table with a row for each period, then you would not need to use dynamic SQL. But that is a larger design question.)
