SQL to Update records of one table with records from another table?

Question

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

Answer 1

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

UPDATE 
    new_table 
INNER JOIN 
    old_table ON (old_table.id = new_table.id)
SET 
    new_table.latitude = old_table.latitude,
    new_table.longitude = old_table.longitude;

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

UPDATE 
    new_table 
SET 
    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.

Answer 2

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

REPLACE INTO bad_latlng (id,name,label,descr,lat,lng)
SELECT
b.id,b.name,b.label,b.descr,g.lat,g.lng
FROM
bad_latlng b JOIN good_latlng g ON(b.id = g.id)