MySQL更新内部连接表查询

我不知道问题出在哪里。使用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

所有字段名称都是正确的。任何想法吗?

350188 次浏览
小开
最佳答案

试试这个:

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
-> 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进行了测试。

小开

SET子句应该是表规范的后。

UPDATE business AS b
INNER JOIN business_geocode 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工作台，请使用以下方法:

update emp as a
inner join department b on a.department_id=b.id
set a.department_name=b.name
where a.emp_id in (10,11,12);

提交答案