MYSQL Update Statement Inner Join Tables?

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 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?

Answer 1

For Your case, you have Correlated Tables. You wil need to use Subquery to achieve your objective.

SQL allows you to use Subquery in the 'UPDATE' 'SET' or 'WHERE clause

For your case, you can use subquery in the ''SET' statement as shown below:

UPDATE business b

SET mapx = (

SELECT g.latitude FROM business_geocode g WHERE b.business_id = g.business_id

AND (b.mapx = '' or b.mapx = 0)

AND g.latitude > 0

) ,

mapy = (

SELECT g.longitude FROM business_geocode g WHERE b.business_id = g.business_id

AND (b.mapx = '' or b.mapx = 0)

AND g.latitude > 0

)

WHERE (b.mapx = '' or b.mapx = 0) ;

Hope this helps.