MySQL Update Inner Join tables query

Question

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  gt  0  All the field names are correct  Any thoughts

User · Answer

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  gt  0

User · Answer

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

User · Answer

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  gt  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 gt  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 gt  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 gt  UPDATE business AS b     - gt  INNER JOIN business geocode AS g ON b business id   g business id     - gt  SET b mapx   g latitude      - gt    b mapy   g longitude     - gt  WHERE   b mapx      or b mapx   0  and     - gt    g latitude  gt  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] MySQL Update Inner Join tables query

Examples related to mysql

Examples related to compiler-errors