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.

  • Android

    Super, just what I needed to know.

  • TeyBobster

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

    • Nick

      Four people can’t understand MySQL. =(

      • Tulanling

        wakakka

  • http://2ndEffect.dk Joggy

    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.

    • Pokemon

      Thanks, Was hoping this was possible.

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

  • http://ohmyfile.com/ ohmyfile.com

    Thank you

  • Harpreet Khera

    Thanks this is what i was looking for…..

  • Anonymous

    thanks, just what i need to know (-:

  • Infected Pixel

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

  • http://richardneililagan.com Richard Neil Ilagan

    super. just what I was looking for.

  • anonymous

    thnx, guy! It works!

  • Gururaj B AShetty

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

  • http://coastalweb.ca Coastal Web

    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.

    • Anonymous

      Cool info. Thanks for adding to the discussion.

      • boonanny

        Nice ! this is the tweak I need.

  • Samthunderbolt

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

  • Anonymous

    Thanks!

  • Ms_ngobeni

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

  • Pieter Kegel

    Thanks! You’re the man!

  • Guest

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

    • Pieterkegel

      Remove the WHERE clause…

  • http://twitter.com/CSS CSS

    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

  • http://www.shoe-heaven.com/ christian louboutin outlet

    oh my!! i love you so much!!

    Thank you so much.

  • Mihai Stefan
  • http://shohan.info Shohan

    Thanks, i was searching for this

  • http://www.walkfront.com Akbar Firdiansyah

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

  • http://lyrics-to.srimp3.com lyrics-to.srimp3.com

    oh! thank you very much

  • Jessy

    Very helpful code. Thank you……..

  • [email protected]

    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.

  • Ranjith

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

  • whatever

    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 :)

  • Hannes

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

  • john

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

  • http://interra.us ivsky

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

  • Steeve

    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 :)

  • nowy papież

    Thank You :D Now i remember syntax.

  • sanjeeviraj

    very useful snippets.