How can I update a field in one table with a field from another table? (SQL)

Answer 1

    Update Courses
    SET Courses.Instructor_ID = Course_Roster.User_ID
    from Courses Inner Join Course_Roster 
    On Course_Roster.CourseID = Courses.Course_ID

This is assuming that your DBMS allows for joins on your update queries. SQL Server definitely allows this. If you cannot do something like this you need to look towards using a subquery.

Answer 2

UPDATE COURSES 
SET COURSES.INSTRUCTOR_ID = COURSE_ROSTER.USER_ID 
FROM COURSES
INNER JOIN COURSE_ROSTER
    ON COURSE_ROSTER.COURSE_ID = COURSES.COURSE_ID

Answer 3

Why do you need the column course.instructor_id if you fill it with COURSE_ROSTER.user_id? Isn't it redundant storage?

Answer 4

Not all database vendors (SQL Server, Oracle, etc.) Implement Update syntax in the same way... You can use a join in SQL Server, but Oracle will not like that. I believe just about all will accept a correclated subquery however

  Update Courses C  
   SET Instructor_ID = 
          (Select User_ID from Course_Roster
           Where CourseID = C.Course_ID)

NOTE: The column User_ID in Course_Roster would probably be better named as InstructorId (or Instructor_Id) to avoid confusion

Answer 5

UPDATE COURSES 
SET INSTRUCTOR_ID = CR.USER_ID 
FROM COURSES C
INNER JOIN COURSE_ROSTER CR   
   ON CR.COURSE_ID = C.COURSE_ID