I have a database of phone numbers and I need a query that assigns a phone number to a given user (sid). The query needs to pull an available phone number (one without a user assigned to it (sid=’’)) and also should pull the phone number with the oldest last_updated datetime stamp. It is possible that multiple records exist with the same timestamp. Because the script may be called from multiple users simultaneously, I believe I need to lock the record until the update is complete so we don’t overwrite one record with another. Preferably the lock would queue the request instead of erroring out. Here is my failed attempt at the query. I’m on MySQL 5.5.42. Any help would be greatly appreciated! Thanks!
UPDATE phone_numbers SET sid='12' , last_update=NOW() , issued_on=NOW() WHERE id IN ( SELECT id FROM phone_numbers WHERE sid='' ORDER BY last_updated DESC LIMIT 1)
The error I’m getting is “This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’”.
I’d prefer to do it as a single statement so it executes as fast as possible and doesn’t require any external scripting.