OraFAQ Forum: SQL & PL/SQL » Updating table with sequentially changing column name

Home » SQL & PL/SQL » SQL & PL/SQL » Updating table with sequentially changing column name (oracle 9i)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Updating table with sequentially changing column name [message #563175]

Thu, 09 August 2012 05:48

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;

Report message to a moderator

Re: Updating table with sequentially changing column name [message #563176 is a reply to message #563175]

Thu, 09 August 2012 05:50

Littlefoot
Messages: 17256
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Dynamic SQL (EXECUTE IMMEDIATE) is what you should search for.

Report message to a moderator

Re: Updating table with sequentially changing column name [message #563185 is a reply to message #563176]

Thu, 09 August 2012 06:29

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;

Report message to a moderator

Re: Updating table with sequentially changing column name [message #563186 is a reply to message #563185]

Thu, 09 August 2012 06:30

Littlefoot
Messages: 17256
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Thank you for the feedback!

Report message to a moderator

Re: Updating table with sequentially changing column name [message #563205 is a reply to message #563186]

Thu, 09 August 2012 10:22

BlackSwan
Messages: 20245
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.

Report message to a moderator

Re: Updating table with sequentially changing column name [message #563521 is a reply to message #563205]

Tue, 14 August 2012 10:58

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.

Report message to a moderator

Re: Updating table with sequentially changing column name [message #563522 is a reply to message #563521]

Tue, 14 August 2012 11:06

BlackSwan
Messages: 20245
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.

Report message to a moderator

Previous Topic:	Roles dependency
Next Topic:	What am I doing wrong here...

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 19 16:46:05 CDT 2013

Total time taken to generate the page: 0.11518 seconds