## How to Retrieve and Update MySQL Rows in a Single Operation Without Subqueries?

Query to Retrieve and Update MySQL Rows in a Single Operation

Combining SELECT and UPDATE queries into a single operation can enhance efficiency and reduce code complexity. This article explores a solution to achieve this objective without using subqueries.

Conundrum

A user sought assistance in combining the following MySQL queries:

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

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

The primary goal was to retrieve the desired rows while simultaneously updating their "time" field to zero.

Ingenious Solution

The solution involves using stored procedures to achieve the desired result. Stored procedures encapsulate a series of SQL statements and can be executed as a unit.

The following procedure named "update_and_retrieve" accomplishes the task:

<code class="mysql">CREATE PROCEDURE update_and_retrieve(IN group_id INT, IN time INT)
BEGIN
    DECLARE updated_ids VARCHAR(255);

    UPDATE table SET time = 0 WHERE group_id = group_id AND time > time;

    SET updated_ids = (SELECT GROUP_CONCAT(fooid) FROM table WHERE group_id = group_id AND time > time);

    SELECT * FROM table WHERE fooid IN (updated_ids);
END;</code>

Procedure Invocation

To invoke the stored procedure, simply execute the following statement:

<code class="mysql">CALL update_and_retrieve(1013, 100);</code>

This will update the "time" field to zero for the qualifying rows and then retrieve and display those updated rows.

Benefits

This approach offers several advantages:

• Avoids unnecessary network round trips by combining multiple operations into a single call.
• Simplifies code structure by eliminating the need for separate queries.
• Enhances performance by reducing the database load.

The above is the detailed content of ## How to Retrieve and Update MySQL Rows in a Single Operation Without Subqueries?. For more information, please follow other related articles on the PHP Chinese website!