为什么我的 MySQL UPDATE JOIN 查询会产生编译错误？

MySQL 更新连接表查询问题

在尝试使用 MySQL 连接查询更新表时，用户遇到了编译错误。这是他们使用的查询：

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

尽管字段名称正确，查询仍无法编译。

解决方案：

根据分析查询，发现将表别名放在列名前面即可解决该错误，如图下面：

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

更新：

用户确认查询仍然导致语法错误后，创建了测试表来验证语法。修改后的查询针对 MySQL 5.5.8 进行了测试，没有产生任何语法错误，如下所示：

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

以上是为什么我的 MySQL UPDATE JOIN 查询会产生编译错误？的详细内容。更多信息请关注PHP中文网其他相关文章！