MYSQL更新语句内部联接表 [英] MYSQL Update Statement Inner Join Tables

问题描述

我不知道问题是什么.使用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 statement

  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.

这篇关于MYSQL更新语句内部联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！