How to Achieve Row Updates and Retrieval in a Single MySQL Query?

Achieving Row Updates and Retrieval in a Single MySQL Query

In MySQL, executing multiple queries can be time-consuming. To optimize performance, combining queries into a single one is desirable. This is the crux of a query posed by Quan: can two queries be merged to return updated rows?

The first query selects rows from a table based on specific criteria, while the second updates rows meeting the same criteria. Combining these queries requires a technique that allows for both reading and writing in a single operation.

One approach is to use a subquery. However, Quan encountered limitations with this method. An alternative solution emerged in a recommendation from https://gist.github.com/PieterScheffers/189cad9510d304118c33135965e9cddb:

SET @uids := null;
UPDATE footable
   SET foo = 'bar'
 WHERE fooid > 5
   AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;

This query leverages the following steps:

1. Initialize a variable (@uids) to null.
2. Use an UPDATE statement to modify rows meeting the criteria while also appending their fooid values to @uids, creating a comma-separated list.
3. Execute a SELECT statement to retrieve the updated @uids value, which contains the list of updated row fooid values.

This solution efficiently combines both row updates and retrieval, providing the desired functionality in a single query.

The above is the detailed content of How to Achieve Row Updates and Retrieval in a Single MySQL Query?. For more information, please follow other related articles on the PHP Chinese website!