Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Updating data in a table dynamically

189396Apr 6 2007 — edited Apr 10 2007
Hi

My database table is emp and has following five columns:
empno
ename
sal
comm
zipcode

And I have a file that has data that needs to be updated in emp table. This file will always have first line as a header. Also it may not have all the columns in the emp table, it may have any of above five coulmns or all of them. Key to update is empno. If we see a '?' we don't need to update that table.

empno ename sal comm
10 ABC ? 10
20 XYZ 100 ?
30 DEF 120 20

So that means I need to dynamically generate an update statement for each record in this file since there is no pattern for ?, and it can be anywhere. And table name, keys everything is coming to me as a parameter. Actually, the keys are stored in a separate table, it can be one key or a combination of keys. Three update statements should be like this:

update emp set ename='ABC', comm=10 where empno=10
update emp set ename='XYZ', sal=100 where empno=20
update emp set ename='DEF', sal=120, comm=20 where empno=30

Any suggestions how to achieve this.

Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 8 2007
Added on Apr 6 2007
13 comments
715 views