MySQL Update Column +1?

Question

I was wondering what would be the easiest way to update a column by +1? I will be updating a post count of a category based on when users submits a new post.

Thanks.

I am sorry, I do not understand. Do you wish to update all rows setting the value of your column to its old value + 1? — Benoit, Oct 05 '10 at 18:23
@Benoit: The OP wants to store & update the post count related to the category. — OMG Ponies, Oct 05 '10 at 18:29

score 53 · Accepted Answer · answered Oct 05 '10 at 18:26

53

The easiest way is to not store the count, relying on the COUNT aggregate function to reflect the value as it is in the database:

   SELECT c.category_name,
          COUNT(p.post_id) AS num_posts
     FROM CATEGORY c
LEFT JOIN POSTS p ON p.category_id = c.category_id

You can create a view to house the query mentioned above, so you can query the view just like you would a table...

But if you're set on storing the number, use:

UPDATE CATEGORY
   SET count = count + 1
 WHERE category_id = ?

..replacing "?" with the appropriate value.

answered Oct 05 '10 at 18:26

OMG Ponies

325,700
82
523
502

2

Thanks. Initially my thought to determine the amount of posts in a category would be to check the post tables for a cat_id matching that category and then add them up. Thought this may be resource consuming or less efficient than writing directly to that table. – Cory Oct 05 '10 at 18:37
1

It's very quick when you have an index on `p.post_id`. – Amadan Oct 06 '10 at 10:56
UPDATE CATEGORY SET count = count + 1; perfect solution. – Ahamadullah Saikat Aug 27 '19 at 09:55

score 37 · Answer 2 · answered Oct 05 '10 at 18:25

37

You can do:

UPDATE categories SET posts = posts + 1 WHERE category_id = 42;

answered Oct 05 '10 at 18:25

Michael Banzon

4,879
1
26
28

score 8 · Answer 3 · answered Oct 05 '10 at 18:25

8

How about:

update table
set columnname = columnname + 1
where id = <some id>

answered Oct 05 '10 at 18:25

Vincent Ramdhanie

102,349
23
137
192

score 5 · Answer 4 · answered Oct 05 '10 at 18:25

5

update post set count = count + 1 where id = 101

answered Oct 05 '10 at 18:25

KoolKabin

17,157
35
107
145

score 3 · Answer 5 · answered Aug 27 '19 at 09:56

3

update table_name set field1 = field1 + 1;

answered Aug 27 '19 at 09:56

Ahamadullah Saikat

4,437
42
39

1

Can you provide more context? The question is nearly nine years old, and the same way has been given in answers multiple times – Nico Haase Aug 27 '19 at 10:29
I'm using database for 6-7 years. & yesterday I found that solution helpful. So please give positive feedback for my answer. – Ahamadullah Saikat Aug 28 '19 at 13:03
Please provide more explanation first - why did you post that answer, while the exact same solution (okay, with another table and column name) has been posted multiple times? – Nico Haase Aug 28 '19 at 13:09

score 2 · Answer 6 · answered Mar 09 '21 at 10:19

2

How to update order column Count value

Try

  UPDATE order SET                                      
    Order_Count = Order_Count + 100
  WHERE 
    Order_ID = '1234'

answered Mar 09 '21 at 10:19

SIAMWEBSITE

171
1
4

score 1 · Answer 7 · answered May 18 '19 at 13:06

1

update TABLENAME
set COLUMNNAME = COLUMNNAME + 1
where id = 'YOURID'

answered May 18 '19 at 13:06

praveenraj4ever

384
1
5
14

MySQL Update Column +1?

7 Answers7