Why Does My MySQL UPDATE JOIN Query Produce a Compile Error?

MySQL Problem with Update Join Tables Query

In an attempt to update tables using a MySQL join query, a user encountered a compile error. Here's the query they used:

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

Despite the correctness of the field names, the query failed to compile.

Solution:

Upon analyzing the query, it was found that the error could be resolved by placing the table aliases before the column names, as shown below:

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

Update:

After the user confirmed that the query still resulted in a syntax error, test tables were created to verify the syntax. The modified query was tested against MySQL 5.5.8 and did not produce any syntax errors, as seen below:

mysql> 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;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

The above is the detailed content of Why Does My MySQL UPDATE JOIN Query Produce a Compile Error?. For more information, please follow other related articles on the PHP Chinese website!