queries - Declaración de actualización MYSQL tablas de unión interna

union mysql php

Question

no tengo idea cual es el problema. Con MySQL 5.0 obtengo un error de compilación cuando intento ejecutar la siguiente declaración de actualización MYSQL

  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

todos los nombres de campo son correctos. ¿Alguna idea?

Answer 1

La cláusula SET debería venir después de la especificación de la tabla.

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

Answer 2

Para MySql WorkBench, utilice a continuación:

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); 

Answer 3

Prueba esto:

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

Actualizar:

Como dijo que la consulta arrojó un error de sintaxis, creé algunas tablas con las que podría probar y confirmé que no hay ningún error de sintaxis en mi consulta:

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

¿Ver? Sin error de sintaxis Probé con MySQL 5.5.8.