Populate the "professor_id" column
Now it's time to also populate professors_id
. You'll take the ID directly from professors
.
Here's a way to update columns of a table based on values in another table:
UPDATE table_a
SET column_to_update = table_b.column_to_update_from
FROM table_b
WHERE condition1 AND condition2 AND ...;
This query does the following:
- For each row in
table_a
, find the corresponding row intable_b
wherecondition1
,condition2
, etc., are met. - Set the value of
column_to_update
to the value ofcolumn_to_update_from
(from that corresponding row).
The conditions usually compare other columns of both tables, e.g. table_a.some_column = table_b.some_column
. Of course, this query only makes sense if there is only one matching row in table_b
.
This exercise is part of the course
Introduction to Relational Databases in SQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Have a look at the 10 first rows of affiliations
___;