I have a table (id, serialnumber, status) of numbers and their statuses, need to take one with status 'unused' and change it's state to something like 'in work'. Can do it this way:
UPDATE serials SET status = 'in work' WHERE serialnumber IN ( SELECT "serialnumber" FROM serials WHERE status = 'unused' ORDER BY id ASC LIMIT 1 ) RETURNING serialnumber;
Is that the right way of selecting and updating a row in concurrent environment? It's unacceptable to take a serial when someone already took it.