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.