**How to Retrieve Data from Affected Rows After an UPDATE Query in MySQL?**

Combining SELECT and UPDATE Queries for Affected Row Data Retrieval in MySQL

MySQL provides powerful query capabilities, including the ability to perform complex operations. One common scenario involves combining SELECT and UPDATE queries to retrieve data affected by the update operation. This approach can streamline processing and eliminate the need for separate queries.

In this particular case, the goal is to combine two queries: a SELECT statement that fetches data based on specific criteria and an UPDATE statement that modifies matching rows. By merging these queries, the aim is to obtain the updated rows' data in a single operation.

Initially, the user attempted using a subquery, but the desired results were not achieved. This prompted the exploration of alternative methods, including a direct combination of SELECT and UPDATE without a subquery.

Solution: Leveraging SET and SELECT Techniques

A resourceful solution was discovered through external research: By employing SET and SELECT techniques, it is possible to achieve the desired outcome. The provided code snippet demonstrates this approach:

<code class="sql">SET @uids := null;
UPDATE footable
   SET foo = 'bar'
 WHERE fooid > 5
   AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;</code>

This solution utilizes the following steps:

• Initializes the variable @uids to null.
• Updates the footable by setting the foo column to 'bar' for rows where fooid is greater than 5.
• Within the UPDATE query, a subquery assigns the updated fooid values to the variable @uids.
• Finally, the SELECT query retrieves the @uids variable, which now holds a comma-separated list of affected fooid values.

The above is the detailed content of **How to Retrieve Data from Affected Rows After an UPDATE Query in MySQL?**. For more information, please follow other related articles on the PHP Chinese website!