Q
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 editor@searchDatabase.com 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.

This was last published in April 2002

Dig Deeper on Oracle and SQL

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.

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

Thank you...This solution worked out well for me. I take it that you meant to say TableA at the end of the middle paragraph "ColW in TableB references ColW in TableB,"

Thanks
AA
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close