Home » SQL & PL/SQL » SQL & PL/SQL » Updating table with sequentially changing column name (oracle 9i)
Updating table with sequentially changing column name [message #563175] Thu, 09 August 2012 05:48 Go to next message
Jaise
Messages: 3
Registered: August 2012
Location: India
Junior Member
Dear All,

I am trying to update all rows of 100 column of a table with '0'. The column name is sequentially increasing one like EMP_1,EMP_2,EMP_3, etc. I tried using the below code but I am getting ora-06550 and ora-00927 error's. Could any one please help in correcting the code

begin
 FOR i in 1..100 loop
    UPDATE EMP_DETAILS  SET EMP_'||i||' =0
 END LOOP;
COMMIT;
END;


Re: Updating table with sequentially changing column name [message #563176 is a reply to message #563175] Thu, 09 August 2012 05:50 Go to previous messageGo to next message
Littlefoot
Messages: 19646
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Dynamic SQL (EXECUTE IMMEDIATE) is what you should search for.
Re: Updating table with sequentially changing column name [message #563185 is a reply to message #563176] Thu, 09 August 2012 06:29 Go to previous messageGo to next message
Jaise
Messages: 3
Registered: August 2012
Location: India
Junior Member
Thank you very much. You helped me to dig into one more topic. The below code worked successfully for me.
begin
 FOR i in 1..100 loop
EXECUTE IMMEDIATE 'update EMP_DETAILS set EMP'||i|| '= 0';
END LOOP;
COMMIT;
END;
Re: Updating table with sequentially changing column name [message #563186 is a reply to message #563185] Thu, 09 August 2012 06:30 Go to previous messageGo to next message
Littlefoot
Messages: 19646
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you for the feedback!
Re: Updating table with sequentially changing column name [message #563205 is a reply to message #563186] Thu, 09 August 2012 10:22 Go to previous messageGo to next message
BlackSwan
Messages: 22837
Registered: January 2009
Senior Member
> The column name is sequentially increasing one like EMP_1,EMP_2,EMP_3
this "design" is flawed & NOT normalized.
Re: Updating table with sequentially changing column name [message #563521 is a reply to message #563205] Tue, 14 August 2012 10:58 Go to previous messageGo to next message
Jaise
Messages: 3
Registered: August 2012
Location: India
Junior Member
Can you please advise how I can correct it. I am looking extracting some data from different tables with the first column having all the required fields (headings,eg: salaries, Current work, location, band etc )and the subsequent columns with the corresponding data for each employees. How I can make this in a legal way? I opt this way for the easiness to compare in excel sheet.
Re: Updating table with sequentially changing column name [message #563522 is a reply to message #563521] Tue, 14 August 2012 11:06 Go to previous message
BlackSwan
Messages: 22837
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


post DDL for existing tables.
Previous Topic: Roles dependency
Next Topic: What am I doing wrong here...
Goto Forum:
  


Current Time: Tue Sep 30 01:30:22 CDT 2014

Total time taken to generate the page: 0.13130 seconds