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

9 comments:

  1. thanks for the script.
    I had the same column name in both tables so had to do a table.columnname in the where

    update FieldTripList
    set FieldTripTeacher = s.CurrentTeacher
    from Student s
    where FieldTripList.fk_StudentID = s.StudentID

    ReplyDelete
  2. Thanks a ton!
    It's precisely what I was looking for!

    Chirag.

    ReplyDelete
  3. Very helpful...

    ReplyDelete
  4. HELPFUL!!! WANT MORE.

    ReplyDelete
  5. Wah, Thanks a lot

    ReplyDelete