sql - MYSQL Update Statement Inner Join Tables


Translate

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?


All Answers
  • Translate

    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.


  • Translate

    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
    

  • Translate

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