Take the 2-minute tour ×

Hi guys I need help here again - I have two tables here both contain like lots of records in the tens of thousands. Now we have one main table whose schema is like this:

ID | Location Name | Label | Description | Longitude | Latitude

And we have another table with:

ID | Location Name | Longitude | Latitude

The issue is that the longitude and latitude entries in the first table are mostly wrong :( - the correct info is in the second table. Assuming that the IDs are corresponding to the same locations in both tables how can I fix up a query that updates the longitude and latitude field of every row in the first table based upon the entries in the second table where the IDs are the same... any ideas here - I'm on MySQL database...

EDIT =====

Note: Both tables have 20 000 and 50 000 rows respectively...

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

With MySQL 4.0+ you should be able to use the INNER JOIN syntax below:

    old_table ON (old_table.id = new_table.id)
    new_table.latitude = old_table.latitude,
    new_table.longitude = old_table.longitude;

Otherwise, you should also be able to do the following:

    latitude = (SELECT latitude FROM old_table WHERE old_table.id = new_table.id),
    longitude = (SELECT longitude FROM old_table WHERE old_table.id = new_table.id);

Make sure that the ID columns in both tables have a unique index. If they are defined as a primary key, this is enough.

share|improve this answer
I keep getting a timeout - actually the rows in both tables are 20 000 and 50 000 respectively... –  Ali Feb 7 '10 at 9:16
Do you have a unique index on the ID columns of both tables? ... If they are defined as a primary key, this is enough. –  Daniel Vassallo Feb 7 '10 at 9:58
add comment

If ID's are primary keys / unique index you could use REPLACE

REPLACE INTO bad_latlng (id,name,label,descr,lat,lng)
bad_latlng b JOIN good_latlng g ON(b.id = g.id)
share|improve this answer
add comment

Your Answer


