Normalize SQL - Set column to primary key identifier based on matching string from another table

Question

Table Visualization

oldCars

carId | color | etc    // Column Name
1     | red   | *
2     | blue  | *
3     | teal  | *
4     | red   | *

carColors

colorId | color | etc  // Column Names
1       | blue  | *
2       | teal  | *
3       | red   | *

newCars

carId | colorId | etc  // Column Names

I am trying to normalize a database. What I would like to do is insert into newCars each row from oldCars but instead of using just the color name, I want to use the primary key colorId from the carColors table.

The result would look like this...

newCars

carId | colorId | etc  // Column Names
1     | 3       | *
2     | 1       | *
3     | 2       | *
4     | 3       | *

This sort of statement seems trivial, but I can't figure it out.

Answer 1

It's just a simple JOIN using the color name as the joining condition.

INSERT INTO newCars
SELECT o.carID, c.colorID, o.etc
FROM oldCars AS o
JOIN carColors AS c ON c.color = o.color