CODEWITHSUNDEEP
mysqlselect
Jon Tackabury
Jon Tackabury

Reputation: 49237

How to increment a counter and return the value in MySQL

I have a table that has a Key column and a Counter column. I need to do something like this:

SELECT counter=counter+1 FROM table WHERE key='mykey'

Basically, I need to increment the Counter column and return the new value. How can I do this in MySQL 5.5?

Upvotes: 20

Views: 11476

Answers (1)

Joel B Fant
Joel B Fant

Reputation: 24756

update mytable set count=last_insert_id(counter+1) where key='mykey'

Then

select last_insert_id()

last_insert_id() can be passed an argument to 'set' it (and return that value), and calling it without an argument will return the value again. The internal state used by last_insert_id() is per-connection, which means that the same update statement issued on another connection will not affect the first.

Reference: last_insert_id()

Upvotes: 30

Related Questions