How to Retrieve the Updated Value After a MySQL UPDATE Operation?

Retrieving Updated Value from MySQL

When performing an update operation in MySQL, the affected rows count is typically returned by default. However, there are scenarios where it's desirable to retrieve the updated value instead.

Consider the following query:

UPDATE item SET `score`=`score`+1 WHERE `id`=1

This query increments the score of an item with the specified ID. By default, the result of executing this query would be the number of rows affected, which is 1 in this case.

However, to retrieve the updated value, a stored procedure can be utilized. A stored procedure allows you to execute a set of SQL statements as a single unit and can be defined as follows:

<code class="sql">DELIMITER $$   -- Change DELIMITER in order to use ; withn the procedure
CREATE PROCEDURE increment_score
(
   IN id_in INT
)
BEGIN
    UPDATE item SET score = score + 1 WHERE id = id_in;
    SELECT score AS new_score FROM item WHERE id = id_in;
END
$$            -- Finish CREATE PROCEDURE statement
DELIMITER ;   -- Reset DELIMITER to standard ;</code>

This stored procedure first updates the score and then selects the new value into an output parameter named new_score. In PHP, you can call this stored procedure and retrieve the updated value as follows:

<code class="php">$result = mysql_query("CALL increment_score($id)");
$row = mysql_fetch_array($result);
echo $row['new_score'];</code>

By using a stored procedure, you can execute both the update and retrieval operations within a single query, reducing the number of queries and improving performance.

The above is the detailed content of How to Retrieve the Updated Value After a MySQL UPDATE Operation?. For more information, please follow other related articles on the PHP Chinese website!