241

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?

Kate Orlova
  • 3,225
  • 5
  • 11
  • 35
Vibration Of Life
  • 3,177
  • 3
  • 22
  • 23

3 Answers3

504

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.

Asaph
  • 159,146
  • 25
  • 197
  • 199
  • I tried that and am getting the same error. -- Error on getting execution plan: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE business as b INNER JOIN business_geocode g ON b.business_id = g.busines' at line 1 – Vibration Of Life Nov 08 '11 at 22:03
  • Please post the results of `show create table business;` and `show create table business_geocode;` so I can test my query a little better. Thanks. – Asaph Nov 08 '11 at 22:06
  • There is no syntax error. I just confirmed it and updated my answer. – Asaph Nov 08 '11 at 22:14
  • 4
    @user719316: There's something fishy *before* that query...are you missing a semicolon? – Bobby Nov 08 '11 at 22:17
  • @Bobby: You're probably right about that. That would explain why the OP is getting a syntax error when it runs fine on my computer. – Asaph Nov 08 '11 at 22:29
  • 2
    @Joakim The `AS` keyword is optional. But since you mentioned it, I added it to the answer for the sake of consistency, since I did use it on the first alias in the same query. – Asaph Nov 30 '12 at 16:49
22

The SET clause should come after the table specification.

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
mathiasfk
  • 1,278
  • 1
  • 19
  • 38
-3

For MySql WorkBench, Please use below :

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);
TroubleShooter
  • 1