Select-And-Update in one statement?

[Ignoramus23298]

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

[Axel Schwenke]

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: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]