how to load specific attributes from two table to another?

I am trying to load attributes from two tables to one table.

I have a Location table:

Id  City       State     Country
===============================
1   New York   New York  USA
2   Portland   Oregon    USA
3   Tokyo      Honshu    Japan

And a User table:

Id  First_Name  Last_Name  Hometown_City  Hometown_State  Hometown_Country
===========================================================================
1   Brett       Burr       New York       New York        USA
2   Bucky       Beaver     Portland       Oregon          USA
3   Ranma       Saotome    Tokyo          Honshu          Japan

I'm creating a new table for users, which instead of containing the separate Hometown fields, has a Hometown_Id foreign key.

Something like:

Id  First_Name  Last_Name  Hometown_Id
========================================
1   Brett       Burr       1
2   Bucky       Beaver     2
3   Ranma    开发者_StackOverflow社区   Saotome    3

However, I'm having a problem with the syntax when inserting the data into the new Users table.

I can insert the non-location based fields easily with a command like:

INSERT INTO newusers (Id, First_Name, Last_Name) 
SELECT DISTINCT Id, First_Name, Last_Name 
FROM users

However, I'm not sure of the correct syntax to then add the values from the location table

Assuming that you have the appropriate constraints, and that the Location table contains unique entries, the following should work:

INSERT INTO newusers (Id, First_Name, Last_Name, Hometown_Id)
SELECT users.Id, users.First_Name, users.Last_Name, locations.Id
FROM users
INNER JOIN locations
ON locations.City = users.Hometown_City
AND locations.State = users.Hometown_State
AND locations.Country = users.Hometown_Country

If you have more than one location needed (such as a 'current residence' entry), alias and join to the locations table (from users) a second time.