Can you combine MySQL UPDATE and SELECT operations into one request to \"own\" a row and retrieve its parameters?

MySQL UPDATE and SELECT in One Request

Many worker applications perform tasks in a loop by accessing a table of tasks in a MySQL database using MySQL's native C APIs. To own a task, an application:

1. Generates a globally unique ID.
2. Executes an UPDATE query to set the guid field to the generated ID for a row with a guid of 0.
3. Executes a SELECT query to retrieve the parameters of the task based on the guid.

Is there a way to combine these steps into a single call to the server, effectively "owning" a row and obtaining its parameters in a single operation?

Answer:

Yes, it is possible to achieve this using an UPDATE query with a subquery:

UPDATE tasks
SET guid = (
    SELECT id
    FROM tasks
    ORDER BY id DESC
    LIMIT 1
)
WHERE guid = 0
RETURNING guid, params;

This query updates the guid field of the row with guid 0 using the highest id from the tasks table as the new guid. It also returns both the updated guid and the parameters of the task in a single row.

The above is the detailed content of Can you combine MySQL UPDATE and SELECT operations into one request to \"own\" a row and retrieve its parameters?. For more information, please follow other related articles on the PHP Chinese website!