How to Correctly Update MySQL Data Using INNER JOIN?

MySQL Query Using Inner Join for Updates

A user recently encountered an error while attempting to execute an UPDATE statement using an INNER JOIN in MySQL 5.0. The error occurred despite using correct field names.

The original query is as follows:

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

The issue lies in the syntax of the query. To resolve it, the tables must be explicitly named in the UPDATE statement. The corrected query is:

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

The user also encountered a syntax error. To confirm that the corrected query does not contain syntax errors, the following tables were created for testing:

CREATE TABLE business (business_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, mapx VARCHAR(255), mapy VARCHAR(255)) ENGINE=INNODB;

CREATE TABLE business_geocode (business_geocode_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, business_id INT UNSIGNED NOT NULL, latitude VARCHAR(255) NOT NULL, longitude VARCHAR(255) NOT NULL, FOREIGN KEY (business_id) REFERENCES business(business_id)) ENGINE=INNODB;

When executing the corrected query against these tables using MySQL 5.5.8, no syntax error occurred. The query result shows that no rows were matched or changed.

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