MySQL 更新内部连接表查询
我不知道问题出在哪里.使用 MySQL 5.0 尝试运行以下 MySQL 更新查询时出现编译错误:
I have no idea what the problem is. Using MySQL 5.0 I get a compile error when attempting to run 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
所有字段名称都正确.有什么想法吗?
All the field names are correct. Any thoughts?
推荐答案
试试这个:
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
更新:
既然你说查询产生了语法错误，我创建了一些表来测试它并确认我的查询中没有语法错误:
Update:
Since you said the query yielded a syntax error, I created some tables that I could test it against and confirmed that there is no syntax error in my query:
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 进行了测试.
See? No syntax error. I tested against MySQL 5.5.8.
相关文章