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 that contain as many records in tens of thousands. Now we have one main table, the scheme of which is as follows:

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

And we have another table with:

ID | Location Name | Longitude | Latitude

The problem is that the longitude and latitude entries in the first table are mostly incorrect :( - the correct information is in the second table. Assuming the identifiers correspond to the same locations in both tables, how can I fix a query that updates the longitude and latitude field of each row in the first table based on the entries in the second table where the identifiers are the same ... any ideas here - I'm based on MySQL ...

EDIT =====

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

Answer 1

With MySQL 4.0+, you can 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 can also 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); 

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

Answer 2

If the identifier is primary keys / unique index, you can 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)