Get Updated Value in MySQL instead of affected rows

Question

I've been trying to find an answer to this question, but haven't found any definitive "yes" or "no" in all my research.

I'm running a simple MySQL query like this:

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

Is there a way for that query to return the updated value, instead of the number of rows affected? Just as a reference, I'm doing this in PHP, so the actual code looks like:

 $sql = "UPDATE item SET `score`=`score`+1 WHERE `id`=1";  $new_value = mysql_query($sql);   //Unfortunately this does not return the new value 

I know I could do a second query and just SELECT the value, but I'm trying to cut down on queries as much as possible. Is there a way?

Answer 1

You can do it with a stored procedure that updates, and then selects the new value into an output parameter. The following returns one column new_score with the new value.

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 ; 

In PHP:

$result = mysql_query("CALL increment_score($id)"); $row = mysql_fetch_array($result); echo $row['new_score']; 

Answer 2

No, there's nothing like postgresql's UPDATE ... RETURNING output_expression in MySQL (yet?).