How to don't lock whole table?

Question

I have two table dsl_checkins and area_distance. In area_distance 3 rows :

distance
area1
area2

Example:

distance = 3456
area1 = phoenix
area2 = houston

So i need get from dsl_checkins table entry where distance is lowest and need carry about usage_flag field. My idea was use following query:

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 get error:

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

Any idea how to do it without lock whole dsl_checkins table?

http://stackoverflow.com/questions/9080403/update-with-order-by-and-limit-not-working-in-mysql ... you can use a subquery instead. — Tim Biegeleisen, Jul 02 '16 at 15:15
@TimBiegeleisen thank you, but i don't understand how i can use it in my case — comalex3, Jul 02 '16 at 19:36

score 0 · Answer 1 · edited May 23 '17 at 12:33

0

Order by is not compatible with Update

UPDATE with ORDER BY and LIMIT not working in MYSQL

edited May 23 '17 at 12:33

Community

1
1

answered Jul 02 '16 at 15:15

Tibor Szasz

3,028
2
18
23

How to don't lock whole table?

1 Answers1