How to avoid multiple column in correlated sub query assignment in MySQL update

Question

I'm attempting to assign the closest location to a community based on the community postcode and using the Haversine formula with SQL described here. I need to return a single scalar value but I can't seem to avoid having the second calculated distance value which is needed to determine the closest location. Help.

UPDATE Community AS c
    JOIN Postcode p on p.id = c.postcode_id
    JOIN (
        SELECT 100.0 AS radius, 111.045 AS distance_unit
    ) AS a
SET c.location_id = (
    SELECT l.id,
        a.distance_unit
            * DEGREES(ACOS(COS(RADIANS(p.latitude))
            * COS(RADIANS(l.latitude))
            * COS(RADIANS(p.longitude - l.longitude))
            + SIN(RADIANS(p.latitude))
            * SIN(RADIANS(l.latitude)))) AS distance
    FROM Location AS l
    WHERE l.latitude
        BETWEEN p.latitude  - (a.radius / a.distance_unit)
            AND p.latitude  + (a.radius / a.distance_unit)
    AND l.longitude
        BETWEEN p.longitude - (a.radius / (a.distance_unit * COS(RADIANS(p.latitude))))
            AND p.longitude + (a.radius / (a.distance_unit * COS(RADIANS(p.latitude))))
    HAVING distance <= a.radius
    ORDER BY distance
    LIMIT 1
)

Gordon Linoff · Accepted Answer · 2015-07-05T19:40:05.653

Using the structure you have, you need to move the distance calculation into the WHERE and ORDER BY clauses:

SET c.location_id = (
    SELECT l.id
    FROM Location AS l
    WHERE l.latitude
        BETWEEN p.latitude  - (a.radius / a.distance_unit)
            AND p.latitude  + (a.radius / a.distance_unit)
    AND l.longitude
        BETWEEN p.longitude - (a.radius / (a.distance_unit * COS(RADIANS(p.latitude))))
            AND p.longitude + (a.radius / (a.distance_unit * COS(RADIANS(p.latitude))))
    AND a.distance_unit
            * DEGREES(ACOS(COS(RADIANS(p.latitude))
            * COS(RADIANS(l.latitude))
            * COS(RADIANS(p.longitude - l.longitude))
            + SIN(RADIANS(p.latitude))
            * SIN(RADIANS(l.latitude)))) <= a.radius
    ORDER BY a.distance_unit
            * DEGREES(ACOS(COS(RADIANS(p.latitude))
            * COS(RADIANS(l.latitude))
            * COS(RADIANS(p.longitude - l.longitude))
            + SIN(RADIANS(p.latitude))
            * SIN(RADIANS(l.latitude)))) 
    LIMIT 1
)

I think you meant ... "into the **`HAVING`** and `ORDER BY` clauses.". (not the `WHERE` clause.) — spencer7593, Jul 05 '15 at 13:48
If I move the distance calculation into the HAVING clause then I get the error "Error Code: 1054. Unknown column 'l.latitude' in 'having clause'" but if I add the calculation to the WHERE clause it appears to work as expected. thx @Gordon Linoff, you just need to change your 'WHERE' to an 'AND' — tazmaniax, Jul 05 '15 at 16:25

How to avoid multiple column in correlated sub query assignment in MySQL update

1 Answers1