Normalizing MySQL table with records of another table

i have 2 tables. The city tables is not normalized because the country information is in plain text. I have added the id_country to the 'city' table (that column is empty).

I need to check for matches between city>country and country>country and then update the city records that matched with the id_country from the country table. At the end i will be able to delete the 'country' column from the city table.

City table

• id_city (1, 2, 3...)
• city (Washington, Guayaquil, Bonn...)
• country (Germany, Ecuador, USA...)
• id_country (currently empty)

Country table

• id_country (1, 2, 3...)
• code (GE, EC, US...)
• country (Germany, Ecuador, USA...)

I have no idea on where to start and if it can be done with a SQL query. My original idea was to sear开发者_如何学运维ch for matches in a php loop but that seems to be a really harder implementation.

You can do this with a JOIN on an UPDATE statement.

UPDATE city c1 INNER JOIN country c2 ON c1.country=c2.country
SET c1.id_country=c2.id_country;

Using an INNER JOIN will make sure that updates only occur for cities that have a matching country value.

Once you've run it, you'll be able to select all those cities that still have a null id_country just in case some of them didn't match. Conversely, once you've determined that all your cities have an id_country, you can delete that column from the city table.

The city tables is not normalized because the country information is in plain text.

Nonsense. Normalization doesn't mean "replace plain text with id numbers". Find whoever taught you that and poke him in the eye with a sharp stick.

Your real problem is that "city" plus "country" isn't sufficient to identify cities, at least in the USA. I think there are at least a dozen different cities named "Washington" in the USA.

Instead of replacing the country name with an id number, you'd be far better off replacing it with the two-letter country code. The codes are human-readable; the id numbers will require an additional JOIN in every query that uses your table of cities.

Something like this should work:

UPDATE city set id_country = (SELECT country.id_country from country WHERE country.country = city.country)