TA貢獻1829條經(jīng)驗 獲得超7個贊
嘗試這個：
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
更新：
既然您說查詢產(chǎn)生了語法錯誤，所以我創(chuàng)建了一些可以對其進行測試的表，并確認查詢中沒有語法錯誤：
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進行了測試。
TA貢獻1802條經(jīng)驗 獲得超4個贊
該SET子句應在表規(guī)范之后。
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
TA貢獻2003條經(jīng)驗 獲得超2個贊
對于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);
舉報