CODEWITHSUNDEEP
mysqlsqlselect
Justin
Justin

Reputation: 45330

SQL Update And Select In A Single Query

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.

Upvotes: 4

Views: 4079

Answers (3)

Raul Marengo
Raul Marengo

Reputation: 2373

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

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

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.

Upvotes: 2

aF.
aF.

Reputation: 66687

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;

Upvotes: 0

Related Questions