UPDATE + JOIN + ORDER BY in one query?

Question

MySQL

There is a table dsl_checkins and area_distance . In area_distance 3 fields :

distance
area1
area2

For example:

distance = 3456
area1 = phoenix
area2 = houston

I need to get a record with dsl_checkins where the distance is the smallest and I need to take care of the usage_flag field (so that there are no races). I thought to do it beautifully with one request:

UPDATE dsl_checkins DC
    join area_distance AD
    on AD.area2 = DC.area
set DC.usage_flag=1,
    DC.id = (SELECT @dsl_id := DC.id)
WHERE DC.active = 1 AND
      DC.offline = 0 AND
      usage_flag = 0 AND
      AD.area1 = 'houston'
ORDER BY AD.distance ASC, RAND()
limit 1;

But I am getting an error

Error Code: 1221. Incorrect usage of UPDATE and ORDER BY

Stackoverflows were given a link to the documentation that you can’t do this, and they give
a link.
But here's how it is:

UPDATE Ratemaster
SET Ratemaster.Rate =
(
    SELECT Rates.Rate
    FROM Rates
    WHERE Ratemaster.user = Rates.user
    ORDER BY Rates.id
    LIMIT 1
)

apply to my request do not understand.
Any ideas?
PS: I feel that I'm doing everything wrong, but I have no ideas. Thank you.

Answer 1

I don't understand this line. Do you sort or randomize?
ORDER BY AD.distance ASC, RAND()