How to Correctly Update Multiple MySQL Tables Using an INNER JOIN?

MySQL Update Inner Join Tables Query

In attempting to update multiple tables using an inner join in MySQL, users may encounter compilation errors. Specifically, the following query results in a compilation error in MySQL 5.0:

UPDATE  b
SET b.mapx = g.latitude,
    b.mapy = g.longitude
FROM business AS b
INNER JOIN business_geocode g ON b.business_id = g.business_id
WHERE (b.mapx = '' OR b.mapx = 0) AND
      g.latitude > 0

To resolve this issue, rearrange the query as follows:

UPDATE business AS b
INNER JOIN business_geocode AS g ON b.business_id = g.business_id
SET b.mapx = g.latitude,
  b.mapy = g.longitude
WHERE  (b.mapx = '' or b.mapx = 0) and
  g.latitude > 0

Note that the above query is syntactically correct. However, when testing the query against existing tables in MySQL 5.5.8, no rows were affected. This could be a different issue that warrants further investigation depending on the specific data and table structure involved.

The above is the detailed content of How to Correctly Update Multiple MySQL Tables Using an INNER JOIN?. For more information, please follow other related articles on the PHP Chinese website!