Can I update a table by adding data from another table?
TABLE1 s_ID, s_name, ownerID 120 NAME <<data here>> TABLE2 ownerID, s_name 650 NAME NAMEI want to get the (new table association) TABLE1.ownerID rows populated with the values from TABLE2.ownerID. I need somthing like:
update TABLE1 set ownerID = table2.ownerID where table1.s_name + ' String' = table2.s_nameI'm using SQL Server 7.0 AND SQL Server 2000.
Here, I would use a simple correlated sub-query.
update TABLE1 set OwnerID = ( select OwnerID from TABLE2 where s_name = table1.s_name + 'String');For more information, you can see the responses I posted to similar questions at http://searchdatabase.techtarget.com/ateAnswers/0,289620,sid13_cid412523_tax289340,00.html.
For More Information
- Dozens more answers to tough SQL questions from Jason Law are available here.
- 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 first published in September 2002