Select-And-Update in one statement?

On my site I often do

SELECT counter from counters where id = 10
UPDATE counter set counter = counter + 1

Can I somehow merge them into one statement?

Like (in C)

SELECT counter++ from counters where id = 10

can that be done?

i

Ignoramus23298 <ignoramus23298@NOSPAM.23298.invalid> wrote:
>
> SELECT counter from counters where id = 10
> UPDATE counter set counter = counter + 1
>
> Can I somehow merge them into one statement?

You can do READ-MODIFY-WRITE in a single statement (and thus:
free from race condition) like this:

UPDATE counters SET counter = (@x:= counter) + 1 WHERE id = 10

However you still need a second statement to read the (old)
value of the counter:

SELECT @x

In case you wonder what @x is - consult the manual
<http://dev.mysql.com/doc/refman/5.0/en/user-variables.html>


HTH, XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/