mysql update 全表查询,MySQL Update内部联接表查询

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?

解决方案

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.