Saturday, August 11, 2007

[sql] Update table with column from another table

For example, if you have a list of students of all ages attending attending a field trip (Table "FieldTripList"), and you want to record who their current teacher is in the student information table (Table "Student")
update FieldTripList 
set FieldTripTeacher = s.CurrentTeacher
from Student s
where fk_StudentID = s.StudentID

More generic example with joining away to two tables: update columns in [TableA], by joining to [TableB] and [TableC] and using the values from those tables
update [TableA]
set TableAValueB = TableBValue,
TableAValueC = TableCValue
from [TableB], [TableC]
where TableAID = TableBID
and TableBID = TableCID