mysql update 内连接_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

更新：

既然您说查询产生了语法错误，所以我创建了一些可以对其进行测试的表，并确认查询中没有语法错误：

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