MYSQL 更新语句内连接表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8057565/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL Update Statement Inner Join Tables
提问by Vibration Of Life
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 statement
我不知道问题是什么。使用 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
all the field names are correct. Any thoughts?
所有字段名称都是正确的。有什么想法吗？
回答by Asaph
Try this:
尝试这个：
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
See? No syntax error. I tested against MySQL 5.5.8.
看？没有语法错误。我针对 MySQL 5.5.8 进行了测试。
回答by mathiasfk
The SETclause should come afterthe table specification.
该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
回答by TroubleShooter
For MySql WorkBench, Please use below :
对于 MySql WorkBench，请使用以下：
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);
