4

Is there a way in SQL (MySQL) to increment a value, and also return the value in a single query. I am trying to ovoid doing two queries like the following:

QUERY 1

UPDATE my_table SET my_col = (my_col + 1) WHERE something = something_else;

QUERY 2

SELECT my_col FROM my_table WHERE something = something_else;

Thanks.

Justin
  • 42,716
  • 77
  • 201
  • 296

3 Answers3

2

To my knowledge there is still no such possibility in MySQL, but take a look at this question for a possible workaround that at least lets you have the select and update work with the same data transactionally.

Community
  • 1
  • 1
Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
0

There is no way to make a select and a update at the same time. If you want to avoid the select you can declare a variable and put there the value, but that will put the last updated row value in the variable.

declare @value int

UPDATE my_table SET my_col = (my_col + 1), @value = (my_col + 1) WHERE something = something_else;
aF.
  • 64,980
  • 43
  • 135
  • 198
  • but then how are they returning the @value? a select would still need to be performed – Taryn Jan 13 '12 at 11:38
  • @bluefeet like I said, this only avoid to select data from the table again. He would have to make the select of the variable. – aF. Jan 13 '12 at 11:40
0

I don't know what scripting language you are using but here is an example on creating a stored procedure in MySQL that returns the updated value so you can update and select in one operation:

Get Updated Value in MySQL instead of affected rows

Community
  • 1
  • 1
Raul Marengo
  • 2,287
  • 1
  • 15
  • 10