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

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?

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


Dig Deeper on Oracle database design and architecture

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.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close