Q
Problem solve Get help with specific problems with your technologies, process and projects.

Declaring variables

I have two tables with the same column names. Table1 has data for a certain time period. Table2 also has the data for the same time period. Table2 has the correct data for col1, col2. I want to update the columns (col1, col2) in table1 with data from columns (col1, col2) in table2 and leave the col3, col4 in table1 untouched. Each time I have to update different columns. Some tables have more than 20 columns.

Example:


            table1            table1    
            (col1,            (col1,
            col2,             col2,
            col3,             col3,
            col4,             col4,
            timeperiod)       timeperiod) 

How can I do this with a script or block declaring variables? Also, how can I control the update for certain columns? This is not a one time thing that I will be doing. I have to do this for several tables whenever the data is not correct.

If the time period is the Primary key in your tables table1 and table2 then you can write the update statement to update col1 and col2 I table1 as follows:

 

 UPDATE  table1 t1
           SET col1 = (SELECT col1 
           FROM   table2 t2 
                                WHERE TRUNC(t1.timeperiod) =  TRUNC(t2.timeperiod)),
       col2 =  (SELECT col1 
           FROM   table2 t2 
                                WHERE TRUNC(t1.timeperiod) =  TRUNC(t2.timeperiod))
WHERE EXISTS (SELECT   'X'
            FROM   table2 t2 
                                WHERE TRUNC(t1.timeperiod) =  TRUNC(t2.timeperiod))
Please that the WHERE condition is not redundant, if you do not specify the WHERE condition for the main 
UPDATE clause, then col1 and col2 will become null in table1 for rows that do not satisfy the equi-join. 

Now for dynamically updating a list of columns, I have created a procedure that you can use that will update the list of column. For executing the update statement above you will type the following at the SQL*PLUS prompt:

exec copy_column('table2', 'table1', 'timeperiod', timeperiod', 'col1, col2', 'trunc');

The trunk function is the function used by a primary key having a data type of DATE. However, you can specify other type of functions or leave it blank for non date primary keys. For the example above, the columns to be updated are col1 and col2. It can be any number of columns.

 
/
create or replace procedure copy_column
    (source_table       IN VARCHAR2,
     destination_table  IN VARCHAR2,
                     pkey_column_source IN VARCHAR2,
                 pkey_column_dest   IN VARCHAR2 DEFAULT           NULL,
    column_list        IN VARCHAR2,
                    pkey_function    IN VARCHAR2  DEFAULT            NULL) Is
      
  sql_str      VARCHAR2(4000) :=    'update ' ||           destination_table           || ' t1 set  ' ;

  column_name     VARCHAR2(30) := null;
  tail_str        VARCHAR2(4000) := column_list;

  pkey_head       VARCHAR2(4000) := NULL;
  pkey_tail       VARCHAR2(1) := NULL;
BEGIN




  If pkey_function is not null then
    pkey_head := pkey_function || '(' ;
    pkey_tail :=')';
  end if;

  while instr(tail_str, ',') > 0 loop
     column_name := rtrim(ltrim(substr(tail_str, 1, instr(tail_str, ',')-1)));
     if column_name is not null then
         sql_str :=    sql_str || 't1.' || column_name || ' = ' || '(select t2.' || column_name || ' from ' || 
                       source_table || ' t2 where ' || pkey_head || 't2.' || pkey_column_source || pkey_tail ||
     ' =' || pkey_head || 't1.' || pkey_column_dest || pkey_tail || '),';

     end if;     
     if instr(tail_str,',') < length(tail_str) then
       tail_str := substr(tail_str, instr(tail_str, ',')+1);
     end if;
  end loop;

  if tail_str is not null then
    column_name := tail_str;
    sql_str :=    sql_str || 't1.' || column_name || ' = ' || '(select t2.' || column_name || ' from ' || 
                       source_table || ' t2 where ' || pkey_head || 't2.' || pkey_column_source || pkey_tail ||
     ' =' || pkey_head || 't1.' || pkey_column_dest || pkey_tail || '),';
  end if;

  
  sql_str := rtrim(sql_Str, ',') || ' where exists (select t3.' || pkey_column_source || ' from ' || source_table || ' t3 where ' || pkey_head 
           || 't3.' || pkey_column_source || pkey_tail ||
         ' =' || pkey_head || 't1.' || pkey_column_dest || pkey_tail || ')';
    

  EXECUTE IMMEDIATE sql_Str;

END;
/

This was last published in January 2005

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close