How to Update Rows and Get Updated IDs in MySQL Without a Subquery?

Combining SELECT and UPDATE Queries in MySQL

Combining SELECT and UPDATE queries into a single operation can be useful for optimizing database performance. In this case, a user wishes to combine the following queries:

SELECT * FROM table WHERE group_id = 1013 and time > 100;

UPDATE table SET time = 0 WHERE group_id = 1013 and time > 100

While a subquery approach may not provide the desired results, there is a solution that can achieve the goal without a subquery. This solution involves using a user-defined variable (@uids) to store the updated row IDs:

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

The above is the detailed content of How to Update Rows and Get Updated IDs in MySQL Without a Subquery?. For more information, please follow other related articles on the PHP Chinese website!