SQL SELECT and UPDATE at the same time

Question

I have a very simple SELECT looking like this:

SELECT path FROM items WHERE id = 17

Now in the table items there is also a column count, which I want to have incremented everytime the above query is executed.

What is the optimal way to do this?

I don't think you can do in a query, you may need to write a procedure for this. — Abhik Chakraborty, Mar 16 '15 at 16:32
If you want to do in a single query, can't you do a select inside an update? http://stackoverflow.com/questions/1955988/mysql-select-inside-update — , Mar 16 '15 at 16:33

score 4 · Accepted Answer · answered Mar 16 '15 at 16:31

4

There is no way to do that in same query.

You can do something like this:

START TRANSACTION;
UPDATE ....
SELECT ....
COMMIT;

answered Mar 16 '15 at 16:31

RNK

score 1 · Answer 2 · answered Mar 16 '15 at 16:33

1

You need to create a stored procedure and then create an atomic transaction using

START TRANSACTION;
UPDATE items set count = (SELECT MAX(count)+1 from items);
SELECT path FROM items WHERE id = 17;
COMMIT;

answered Mar 16 '15 at 16:33

Sim1

score 0 · Answer 3 · answered Mar 16 '15 at 16:31

0

try combining the update and select into a single call, the response from the SELECT should be returned

UPDATE items SET count = count + 1; SELECT path FROM items WHERE id = 17

answered Mar 16 '15 at 16:31

dbinns66

3 Answers3