MySQL: Increment an exisitng value

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.

38 Responses to “MySQL: Increment an exisitng value”

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

    Super, just what I needed to know.

    Reply

  2. 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

  3. 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

    • December 14, 2013 at 2:43 am, Pokemon said:

      Thanks, Was hoping this was possible.

      No need for multiple querying just to see if a value has changed since your previuos query.

      Reply

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

    Thank you

    Reply

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

    Thanks this is what i was looking for…..

    Reply

  6. December 29, 2009 at 11:23 am, Anonymous said:

    thanks, just what i need to know (-:

    Reply

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

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

    Reply

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

    super. just what I was looking for.

    Reply

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

    thnx, guy! It works!

    Reply

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

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

    Reply

  11. 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

  12. July 06, 2010 at 2:15 pm, Samthunderbolt said:

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

    Reply

  13. July 06, 2010 at 5:02 pm, Anonymous said:

    Thanks!

    Reply

  14. July 19, 2010 at 10:45 am, Ms_ngobeni said:

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

    Reply

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

    Thanks! You’re the man!

    Reply

  16. 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

  17. 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

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

    oh my!! i love you so much!!

    Thank you so much.

    Reply

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

    http://bit.ly/lVwd56 :)

    Reply

  20. September 10, 2011 at 7:41 am, Shohan said:

    Thanks, i was searching for this

    Reply

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

    This is what I need, thank you very much :D

    Reply

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

    oh! thank you very much

    Reply

  23. November 29, 2011 at 12:10 am, Jessy said:

    Very helpful code. Thank you……..

    Reply

  24. 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

  25. August 07, 2012 at 5:13 pm, Ranjith said:

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

    Reply

  26. 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

  27. 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

  28. 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

  29. 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

  30. 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

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

    Thank You :D Now i remember syntax.

    Reply

  32. June 25, 2013 at 1:48 am, sanjeeviraj said:

    very useful snippets.

    Reply

Leave a Reply