MYSQL更新语句内部联接表

Question

我不知道是什么问题。使用MySQL 5.0尝试运行以下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

所有字段名称都是正确的。有什么想法吗？

Answer 1

尝试这个：UPDATE business AS bINNER JOIN business_geocode AS g ON b.business_id = g.business_idSET b.mapx = g.latitude,  b.mapy = g.longitudeWHERE  (b.mapx = '' or b.mapx = 0) and  g.latitude > 0更新：既然您说查询产生了语法错误，所以我创建了一些可以对其进行测试的表，并确认查询中没有语法错误：mysql> create table business (business_id int unsigned primary key auto_increment, mapx varchar(255), mapy varchar(255)) engine=innodb;Query OK, 0 rows affected (0.01 sec)mysql> 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;Query OK, 0 rows affected (0.01 sec)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 5.5.8进行了测试。

Answer 2

该SET子句应在表规范之后。UPDATE business AS bINNER JOIN business_geocode g ON b.business_id = g.business_idSET b.mapx = g.latitude,  b.mapy = g.longitudeWHERE  (b.mapx = '' or b.mapx = 0) and  g.latitude > 0

Answer 3

对于MySql WorkBench，请在下面使用：update emp as ainner join department b on a.department_id=b.idset a.department_name=b.namewhere a.emp_id in (10,11,12);