CODEWITHSUNDEEP
sqloracle-databaseplsql
Bill
Bill

Reputation: 1247

updating a certain field based on a variable value

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;

Upvotes: 1

Views: 41

Answers (1)

Dave Costa
Dave Costa

Reputation: 48111

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.)

Upvotes: 4

Related Questions