MySQL: Increment an exisitng value

Posted by Quinn McHenry in MySQL

This slick MySQL syntax allows you to increment or decrement an existing number in a table without first having to read the value. This is a nice way to increment an access counter.

To increment the value ‘counter’ by one for the row in table ‘images’ where ‘image_id’ is ’15′, use:

UPDATE images SET counter=counter+1 WHERE image_id=15

To decrement the value, use ‘counter=counter-1′ instead. Incrementing or decrementing by other values (or using whatever valid arithmetic arm flexing you need) will work, too.

About Quinn McHenry

Quinn was one of the original co-founders of Tech-Recipes. He is currently crafting iOS applications as a senior developer at Small Planet Digital in Brooklyn, New York.
View more articles by Quinn McHenry

The Conversation

Follow the reactions below and share your own thoughts.

36 Responses to “MySQL: Increment an exisitng value”

December 10, 2008 at 4:52 pm, Android said:

Super, just what I needed to know.

Reply
March 31, 2009 at 12:33 am, TeyBobster said:

What would be the syntax for decrementing/incrementing a number and not an image?

Reply
- February 03, 2011 at 5:38 am, Nick said:
  
  Four people can’t understand MySQL. =(
  
  Reply
  - March 19, 2011 at 5:05 pm, Tulanling said:
    
    wakakka
    
    Reply
April 11, 2009 at 12:37 pm, Joggy said:

He ain’t incrementing an image, that is just the name of the table, and pretty sure that ‘counter’ is a tuple in the table, and that it’s an Integer.

Reply
August 29, 2009 at 8:09 am, ohmyfile.com said:

Thank you

Reply
October 23, 2009 at 10:48 am, Harpreet Khera said:

Thanks this is what i was looking for…..

Reply
December 29, 2009 at 11:23 am, Anonymous said:

thanks, just what i need to know (-:

Reply
January 06, 2010 at 11:22 am, Infected Pixel said:

thanks .. i wanted it for my shopping cart product views

Reply
January 15, 2010 at 12:50 pm, Richard Neil Ilagan said:

super. just what I was looking for.

Reply
January 30, 2010 at 9:39 pm, anonymous said:

thnx, guy! It works!

Reply
February 09, 2010 at 6:21 am, Gururaj B AShetty said:

Nice one.this is the thing I am looking for..

Reply
June 18, 2010 at 10:39 pm, Coastal Web said:

If the table you’re updating has a unique key such as an ID or URL field, you can extend an INSERT statement to include the above UPDATE in one go:

INSERT INTO images (name, counter) VALUES (‘blah.gif’, 1) ON duplicate KEY UPDATE counter = counter + 1

Assuming name needs to be unique.

Reply
- June 19, 2010 at 2:58 pm, Anonymous said:
  
  Cool info. Thanks for adding to the discussion.
  
  Reply
  - April 03, 2011 at 1:43 pm, boonanny said:
    
    Nice ! this is the tweak I need.
    
    Reply
July 06, 2010 at 2:15 pm, Samthunderbolt said:

That’s exactly what I was looking for….thanks!

Reply
July 06, 2010 at 5:02 pm, Anonymous said:

Thanks!

Reply
July 19, 2010 at 10:45 am, Ms_ngobeni said:

3 Years later and this is still helping people!!! thanks

Reply
January 06, 2011 at 8:53 am, Pieter Kegel said:

Thanks! You’re the man!

Reply
February 10, 2011 at 2:29 pm, Guest said:

If I want to increment all Ids then what should i Do

Reply
- March 05, 2011 at 1:21 pm, Pieterkegel said:
  
  Remove the WHERE clause…
  
  Reply
April 19, 2011 at 1:07 am, CSS said:

Awesome Thanks Just to help others out.. here is what I used to update a price column in an Open Cart store by $2 in phpmyadmin:

UPDATE `DBNAMEHERE`.`product` SET `price` = `price`+2

Replace DBNAMEHERE with your db name

Reply
May 09, 2011 at 12:04 am, christian louboutin outlet said:

oh my!! i love you so much!!

Thank you so much.

Reply
June 06, 2011 at 3:21 pm, Mihai Stefan said:

http://bit.ly/lVwd56

Reply
September 10, 2011 at 7:41 am, Shohan said:

Thanks, i was searching for this

Reply
October 15, 2011 at 7:37 am, Akbar Firdiansyah said:

This is what I need, thank you very much

Reply
October 20, 2011 at 8:41 pm, lyrics-to.srimp3.com said:

oh! thank you very much

Reply
November 29, 2011 at 12:10 am, Jessy said:

Very helpful code. Thank you……..

Reply
April 25, 2012 at 7:45 pm, len@lenfarneth.com said:

Wow You the man! I was gonna write a whole new table and join two together for a video voting website but this is jamming! You just saved me a lot of work! thanks.

Reply
August 07, 2012 at 5:13 pm, Ranjith said:

Thanks a lot.. this is wat I was looking for..

Reply
August 15, 2012 at 5:16 am, whatever said:

really helpful but can i do this for multiple records , like this

UPDATE images SET counter=counter+1 WHERE image_id=15 AND image_id=17 AND image_id=9

thanks in advance

Reply
August 16, 2012 at 9:26 am, Hannes said:

Hi! Try this:
UPDATE images SET counter=counter+1 WHERE (image_id=15 OR image_id=17 OR image_id=9)

Reply
November 09, 2012 at 7:16 pm, john said:

This article really helps a lot. I cant beleive i can just get dis with ease.

Reply
January 22, 2013 at 6:54 am, ivsky said:

Thanks, help a lot.

And this must be seen:

dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

LAST_INSERT_ID()

“INSERT … ON DUPLICATE KEY UPDATE Syntax”.

Reply
February 18, 2013 at 4:18 pm, Steeve said:

I needed to create or update, and found that I could use on duplicate key, and update to accomplish what I wanted,

INSERT INTO `sentiment_counts` (foo,bar,counter) VALUES (12345,’baz’,1) ON DUPLICATE KEY UPDATE counter=counter+1;

Thanks for pointing me in the right direction

Reply
March 04, 2013 at 10:27 am, nowy papież said:

Thank You Now i remember syntax.

Reply