5 Replies Latest reply: Jan 16, 2011 3:44 PM by Cloud Nines Bliss RSS

    How to update the column value for multiple tables

      Hello Friends,

      I have group of tables ( say tbl1, tbl2, tbl3,tbl4, tbl5 ) with same column name say x_type_cd .

      I want to replace - ( hiphen ) characters of the column values with underscore (_) for all the tables..

      select x_type_cd from tbl1;


      After replacing hyphen to underscore the data is ..

      select x_type_cd from tbl1;


      Can any one suggest a solution for this ..
      I want to have a script to implement this change.

        • 1. Re: How to update the column value for multiple tables
          Frank Kulash
          Hi, Kumar,

          Sure, that might be a good exercise in PL/SQL.

          before you actually try anything like this, consult your DBA. Even with the WHERE clauses, you'll probably be generating lots of redo, and your DBA might want to know about that in advance.

          Query user_tab_columns to find VARCHAR2 (or other string) columns, sorting them by table_name and column_name.
          For every row that you find, add something to two strigs, set_txt, which will utimately look like this
          UPDATE  table_x
          SET     column_1     = REPLACE (column_1, '-', '_')
          ,     column_2     = REPLACE (COLUMN_2, '-', '_')
          and where_txt, which will look like this:
          WHERE     INSTR (column_1, '-')       > 0
          OR     INSTR (column_2, '-')       > 0
          After doing this for the last column of each table, concatenate these into a string such as
          UPDATE  table_x
          SET     column_1     = REPLACE (column_1, '-', '_')
          ,     column_2     = REPLACE (COLUMN_2, '-', '_')
          WHERE     INSTR (column_1, '-')       > 0
          OR     INSTR (column_2, '-')       > 0
          and execute it, using. EXECUTE IMMEDIATE.

          Use the analytic ROW_NUMBER function to determine if a given row is the first for a table (this will determine whether you will add 'SET' or ',' to set_txt) or if the column is the last for the table (in which case you'll want to use EXECUTE IMMEDIATE).

          If you get stuck, post your best attempt at the code here, and describe exactly what the problem is.
          • 2. Re: How to update the column value for multiple tables
            you can try
            FOR I IN SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME IN ( your list of tables in ' ' & seperated by ,)
            UPDATE I.TABLE_NAME 
                   SET    column_1     = REPLACE (column_1, '-', '_');
            END LOOP;
            i am not sure about that kind of update stmt, i never tried
            if you wanna alter it then u can use dynamic sql for the update stmt...

            Edited by: kiran on Jan 14, 2011 3:23 PM
            • 3. Re: How to update the column value for multiple tables
              But, the problem is you'd be updating all columns irrespective of whether it contains '-' or not.
              • 4. Re: How to update the column value for multiple tables
                Hello I want to create a procedure to do this task.

                I have altogether 40 tables and using procedure I want to update all the 40 table columns values

                Can you please suggest using a procedure or as a script ( so that DBA will execute the script ) .

                Appreciate your quick reply.

                • 5. Re: How to update the column value for multiple tables
                  Cloud Nines Bliss
                  Hi Kumar,

                  It really is a good practice to know how to update multiple column values. From my point of view, you can use a procedure declared inside a PL/SQL block and use it to dynamically update the columns in all the table you want updated. Also you can try using the dba_tab_columns data dictionary table to get the column name for the table that is initialized using a collection. If this is a bit vague, I'll try giving you an example. Please find below the sequence of code (not a working code since i cannot test it for the mean time but just the logic alone might help).

                     --declare a collection for the list of tables you want their columns updated (You will loop to this table names and pass the table names to a procedure which will update the columns of the passed table name)
                     TYPE tbl_col_type is TABLE of VARCHAR2(50);
                     L_table_col  tbl_col_type := tbl_col_type('Table_Name1','Table_Name2','Table_Name3');
                    --delare a procedure that will update all the tables
                    PROCEDURE UPD_TBL(I_table_name      IN   VARCHAR2) IS --This procedure takes 1 input and that's the table name passed from the collection.
                     cursor C_columns is
                     select column_name
                     from dba_tab_columns
                     where table_name = I_table_name; --This curor will get all the column names of the table passed, example - get all the columns of the table and validate it later on in the update syntax.
                  for rec in C_columns LOOP
                    update I_table_name
                         set rec.column_name =  .... For this one try using the replace function discussed in this thread. rec.column_name is the column name taken from the cursor, this will loop through all the columns. if you don't want to update everything, just make a condition in the SQL update statement. e.g. set rec.column_name = ...
                  where rec.column_name = the name of column you want updated.
                  END LOOP;
                     when OTHERS then
                  END UPD_TBL;
                     FOR rec IN L_table_col.first ..  L_table_col.last LOOP --This will loop to the declared collection, passing the table name to the declared procedure.
                     END LOOP;
                     when OTHERS then
                  I'm still not sure if the logic will work. But it's a good practice to know how to update multiple column values.
                  Hope this helps.