MySQL UPDATE and SELECT in one pass

Question

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?

Answer 1

try like this

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

above code worked for me