Can I update a table by adding data from another table?
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.