UPDATE one table based on a condition in another
Is it possible to update a field in one table depending on a condition of a field in another? If so how is this done?
Yes, but the exact syntax will depend on your particular database system.
For example, in SQL Server:
update table1 set fldX = T2.fldY from table1 T1 inner join table2 T2 on T1.keyfld = T2.keyfld where T2.fldZ = 'foo'
... whereas in Microsoft Access:
update table1 inner join table2 on table1.keyfld = table2.keyfld set table1.fldX = table2.fldY where table2.fldZ = 'foo'
Just another example of different databases implementing the same thing in different ways.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- 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, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.