MySQL UPDATE and SELECT in one pass


I have a MySQL table of tasks to perform, each row having parameters for a single task.
There are many worker apps (possibly on different machines), performing tasks in a loop.
The apps access the database using MySQL's native C APIs.

In order to own a task, an app does something like that:

  • Generate a globally-unique id (for simplicity, let's say it is a number)

  • UPDATE tasks
    SET guid = %d
    WHERE guid = 0 LIMIT 1

  • SELECT params
    FROM tasks
    WHERE guid = %d

  • If the last query returns a row, we own it and have the parameters to run

Is there a way to achieve the same effect (i.e. 'own' a row and get its parameters) in a single call to the server?

Best Solution

try like this

UPDATE `lastid` SET `idnum` =  (SELECT `id` FROM `history` ORDER BY `id` DESC LIMIT 1);

above code worked for me