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

Updating multiple columns from another table

There are two tables in our database. Let's assume that they are A & B. Table A has 10 columns, and table B has 8 columns. There are some 10,500 rows in table A, and 10,000 rows in table B. There are four common columns in both the tables. One column in table B is a foreign key to a column in table A. Now the problem is to update the table A's three columns out of the four common columns between these two tables. These 3 columns in table A are to be updated based on the values present in table B's corresponding 3 columns, which has the foreign key to primary key in table A. Is there a way to update all the three columns in one shot or another way that update can be done?

Assuming ColW, ColX, ColY, and ColZ are the columns common to the two tables and ColW in TableB references ColW in TableB, here is how I would update TableA:

update TableA
  set ColX = ( select ColX from TableB 
                 where ColW = TableA.ColW ),
      ColY = ( select ColY from TableB 
                 where ColW = TableA.ColW ),
      ColZ = ( select ColZ from TableB 
                 where ColW = TableA.ColW );
Your database management system should be able to see that for each row in TableA, all three correlated sub-queries access the same row in TableB. Therefore, it should optimize the query so that it is only accessing the row in TableB once for each row in TableA.

For More Information

  • What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL