CODEWITHSUNDEEP
mysqlinsert-update
dzm
dzm

Reputation: 23554

INSERT ... SELECT, WHERE

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!

Upvotes: 0

Views: 319

Answers (3)

PIM
PIM

Reputation: 314

How about:

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

Upvotes: 0

Sean Carpenter
Sean Carpenter

Reputation: 7731

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.

Upvotes: 0

Yuliy
Yuliy

Reputation: 17728

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

Upvotes: 2

Related Questions