Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

how to update tables based on the metadata where number of columns to be updated are defined..

itzkashiNov 28 2023

hi ,

I want to update the tables from the metadata ref_tab and set the column values to NULL. I tried using for - loop to pick the table and pass columns one by one but since the table contains a huge volume of data it took more time to update.

I want to generate the query that will fetch all the columns at once for updation and also the number of columns are not fixed it can be more or less .

Please advise on this.

sample data:

create table ref_tab (src_tab varchar2(30),src_col varchar2(30)) ; 
             insert into ref_tab values ('TAB1','FNAME');
             insert into ref_tab values ('TAB1','LNAME');
             insert into ref_tab values ('TAB1','PHONE');
             insert into ref_tab values ('TAB2','FNAME');
             insert into ref_tab values ('TAB2','LNAME');
             COMMIT;
             
        
        expected output :     
             UPDATE TAB1
             SET FNAME = NULL , LNAME=NULL,PHONE=NULL;
             
             UPDATE TAB2
             SET FNAME = NULL , LNAME=NULL;
             
                     
Comments
Post Details
Added on Nov 28 2023
2 comments
97 views