Jump to content

Insert field into other table when 2 other fields match


ErikD

Recommended Posts

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. :shrug:

 

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

 

 

 

 

Link to comment
Share on other sites

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")

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.