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?

score 23 · Accepted Answer · edited Jun 05 '17 at 02:27

23

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'];

edited Jun 05 '17 at 02:27

Martin Rüegg

815
9
14

answered Sep 16 '11 at 14:24

Michael Berkowski

267,341
46
444
390

1

Is the performance of doing this in a stored procedure any better than just doing two SQL queries? – jwegner Sep 16 '11 at 14:46
@jwegner Performance will probably be a little bit better because the RDBMS doesn't have to parse either of the queries. Both are already compiled in the procedure on the RDBMS' side. – Michael Berkowski Sep 16 '11 at 14:48
Is there a reason why `id` is compare to `id_in` in the `UPDATE` query and `in_id` in the `SELECT` query? – Keyslinger Dec 13 '12 at 16:23
@jwegner, then it appears to me that you accidentally reversed "in" and "id" in the variable declaration and the first query. – Keyslinger Dec 13 '12 at 16:32
@Keyslinger I see what you mean - just a typo, fixed. – Michael Berkowski Dec 13 '12 at 16:36
I strongly believe that the performance gain of this over two queries is negligible. Especially with queries as simple as these. – taylorcressy Nov 12 '14 at 11:28
@taylorcressy I would agree too, for basic usage. That old comment should read more like _"On a massive scale, performance will probably be a little bit better..."_. At a normal, small webapp scale, there will probably be no measurable benefit. – Michael Berkowski Nov 12 '14 at 17:07

score 12 · Answer 2 · answered Sep 16 '11 at 14:22

12

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

answered Sep 16 '11 at 14:22

VolkerK

95,432
20
163
226

3

Doesn't really help with the question (if there is actually an answer), but +1 for an interesting piece of info! – Jonnix Sep 16 '11 at 14:24

score 2 · Answer 3 · answered Aug 09 '15 at 05:44

If you don't want to run another Query SELECT then here is another way to do it. I have modified Mr. Berkowski code for reference:

DELIMITER $$
CREATE PROCEDURE increment_score
(
   IN id_in INT
)
BEGIN
    set @newScore := null;
    UPDATE item SET score = IF((@newScore := score+1) <> NULL IS NULL, @newScore, NULL) WHERE id = id_in;
    SELECT @newScore;
END
DELIMITER ;

score 0 · Answer 4 · answered Sep 16 '11 at 14:24

0

No you cant. You could make a function or stored procedure that could do the insert and return the updated value but that would still require you to execute two queries from within the function or stored procedure.

answered Sep 16 '11 at 14:24

SnatchFrigate

352
2
7

score -1 · Answer 5 · answered Sep 16 '11 at 14:36

-1

You can create a trigger, and you will know everything about the modifications.

answered Sep 16 '11 at 14:36

Devart

119,203
23
166
186

Get Updated Value in MySQL instead of affected rows

5 Answers5

Linked

Related