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
)

Answer 1

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
)