Using Table 2 values for Table 1 when column names are the same

I want to implement the following algorithm: 
 Select all column names from Table1;
 Select all column names from Table2;
 For each column in Table1 {
 	if column exists in Table2 then
 		Table1.column.value = Table2.column.value;
 }
What is the best way to implement this?

    Requires Free Membership to View

    Login

The best way to do this is to use PL/SQL and the EXECUTE IMMEDIATE command. Something similar to the following: 

DECLARE
   CURSOR c1 IS select column_name 
                from user_tab_columns
                where table_name='table1'
                INTERSECT 
                select colum_name
                from user_tab_columns
                where table_name='table2';
   col_name USER_TAB_COLUMNS.COLUMN_NAME%TYPE;
   update_string VARCHAR2(255);
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO col_name;
      EXIT WHEN c1%NOTFOUND;
      update_string:='UPDATE table1 SET '||col_name||
                     '=table2.'||col_name;
      EXECUTE IMMEDIATE update_string;
   END LOOP;
END;
/
This PL/SQL block has a cursor which finds all columns of the same name in both tables. Then, for each of those columns, an UPDATE command is dynamically built and then executed. My UPDATE command did not take into account that you may want a WHERE clause to limit which rows in the table get updated. You'll have to supply that if applicable.

For More Information


This was first published in November 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top