INSERT ... SELECT, WHERE

Question

I have two tables, one called countries and one called country. Countries has both countries and states, but no ISO for the countries. The country table has the country names with ISO, but no states. I'm trying to do an insert select to update a new ISO column in the countries table and populate it based on the country table (in other words, moving ISO from one table to the other).

Here's what I have:

INSERT countries (country_iso) SELECT country.iso FROM countries,country WHERE countries.name = country.printable_name

All this did, was put the iso's at the end of the countries table. It didn't use the WHERE countries.name = country.printable_name (which are the two columns that match).

Any ideas what I am doing wrong?

Thanks!

Answer 1

INSERT creates new rows. It looks like you want to UPDATE the existing rows instead.

Answer 2

You need to run an update, not an insert:

UPDATE countries set country_iso = country.iso
FROM countries INNER JOIN country on countries.name = country.printable_name

An insert creates new rows while an update modifies existing rows.

Answer 3

How about:

UPDATE countries,country SET countries.country_iso=country.iso WHERE countries.name=country.printable_name