Update different columns based on variable value

[spicysudhi]

Hi

I want to update a table (in PL/SQL) based on the value in the variable WEEK_NO. In a lengthy statement my line look like this
IF WEEK_NO = 1 THEN
UPDATE TABLE myTable Set WEEK1_QTY = xQty WHERE <condition>;
ELSIF WEEK_NO=2 THEN
UPDATE TABLE myTable Set WEEK2_QTY = xQty WHERE <condition>;
ELSIF WEEK_NO=3 THEN
UPDATE TABLE myTable Set WEEK3_QTY = xQty WHERE <condition>;
ELSIF WEEK_NO=4 THEN
UPDATE TABLE myTable Set WEEK4_QTY = xQty WHERE <condition>;
END IF;

Can someone help me in writing one single line to dynamically generate update statement.

thanks in advance.
Sudhi

 

[carp]

BEGIN
.
.
.
EXECUTE IMMEDIATE 'UPDATE my_table SET week'
                  ||to_char(week_no)
                  ||'= xQty WHERE <condition>';
.
.
.
END;

 

[sem]

I think that Carp meant

BEGIN
.
.
.
EXECUTE IMMEDIATE 'UPDATE my_table SET week'
                  ||to_char(week_no)
                  ||'[b]QTY[/b]= :1 WHERE <condition>' using xQty;
.
.
.
END;

Regards, Dima

http://seminihin.narod.ru

 

[spicysudhi]

thanks very much. i was trying Carp's solution, getting error message. Dima's lines solved it. many to both of you.
regards,
sudhi