Replace all the records in a column with a new value given in another table

[Bluejack17]

Hi There


How can i replace all city names in the cities table with the ID for that city from the cityID table.
Thank You In Advance.

[Bob Fitz]

Perhaps something like:

UPDATE Cities INNER JOIN [City Code] ON Cities.City = [City Code].City SET Cities.City = [ID];

and then change the data type of [City Names] to something numeric rather than text.

Alternatively, you could use a query to retrieve the [ID] from matching Cities. Something like:

SELECT [City Code].City, [City Code].ID
FROM Cities INNER JOIN [City Code] ON Cities.City = [City Code].City;

BACK UP YOUR DB BEFORE TRYING

[Micron]

IMO you should add a numeric CityID field first, populate that, then remove the cities text field. Otherwise, you'll be trying to join a text to a numeric field.
Bob, maybe you tried that sql for this situation, so I'd be full of it if I said that I suspect the inner join (both field records are equal) won't work when one field is a number and the other is text. Also, in 2nd pic ID1 field (that's a bad name for any field) looks like an autonumber and you can't update that field, thus adding the number field first would be required.