Why Does My MySQL UPDATE Query with INNER JOIN Produce a Syntax Error?

Update Query Using Inner Join with MySQL: Troubleshooting a Syntax Error

You encounter a syntax error when executing the following MySQL update query:

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

Here's a revised version of your query that should eliminate the syntax error:

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

Assessment

The original query had the table alias incorrectly defined for the business table; it should have been b in your query as you have it there. That was the only syntax issue.

Additional Notes

Your error message is missing. Providing it would help pinpoint the exact cause of the issue. Additionally, verify that the field types in your tables (e.g., mapx and mapy) match the data you're trying to insert.

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