Insert field into other table when 2 other fields match

[ErikD]

Ok sorry for the long title. There is no shorter explanation of my problem. I will try to explain the whole story as clearly as possible...

 

I am building a program with city names over a period of 100s of years. Cities change their names over time so this database can contain the same set of coordinates with different names.

 

I had one table containing over 700.000 cities with their coordinates.

 

My goal was to

(a) extract the unique coordinate pairs (lat+long) and give them an id and put them in another table and

(b) putting those coordinate id into an extra column called coord_id in the first table.

 

The first one i worked out, by doing:

 

INSERT lat_long (lat, `long`) SELECT DISTINCT lat, long FROM cities

 

Correct me if I'm wrong but it seemed to work.

 

I am stuck at step 2.

 

I tried:

 

INSERT cities (coord_id) SELECT id FROM lat_long, cities WHERE lat_long.lat = cities.lat AND lat_long.`long` = cities.`long`

 

But this didn't work out. Probably because the id field is also present in the cities table. I have tried others to, which i can't remember. Could someone point me in the right direction?

 

ErikD

 

 

 

 

[mikosiko]

for your second step you need an UPDATE no an INSERT

[The Little Guy]

why can't you do it on your first query? You can do it without distinct by doing:

 

create table lat_long (coord_id int unsigned primary key, lat decimal(18, 9), long decimal(18, 9));
create unique index lat_long (lat, long) on lat_long;
insert ignore into lat_long (coord_id, lat, long) select id, lat, long from cities;

 

How this works....

1.) First we create our table (you probably already have that)

2.) Next we add a unique index on lat, long so we don't have any duplicates (used for the "ignore" on the following line).

3.) Finally we select every thing from cities and insert into lat_long, if the lat, long is already in there don't insert again (hence "ignore")