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
  • 38,686
  • 72
  • 185
  • 276

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
  • 170,943
  • 22
  • 265
  • 283
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.
  • 62,344
  • 41
  • 131
  • 195
  • 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,217
  • 1
  • 14
  • 9