CODEWITHSUNDEEP
sqlsql-update
CheeseConQueso
CheeseConQueso

Reputation: 6051

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

Two tables:

COURSE_ROSTER - contains

COURSES - contains

What would the UPDATE sql syntax be? I am trying this, but no good... I'm missing something and I can't find it online.

UPDATE COURSES 
SET COURSES.INSTRUCTOR_ID = COURSE_ROSTER.USER_ID 
WHERE COURSE_ROSTER.COURSE_ID = COURSES.COURSE_ID

Upvotes: 1

Views: 4545

Answers (5)

vzczc
vzczc

Reputation: 9390

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

Upvotes: 1

TheTXI
TheTXI

Reputation: 37905

    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.

Upvotes: 5

Charles Bretana
Charles Bretana

Reputation: 146597

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

Upvotes: 4

tuinstoel
tuinstoel

Reputation: 7316

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

Upvotes: 2

Cade Roux
Cade Roux

Reputation: 89741

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

Upvotes: 2

Related Questions