MySQL Update Column +1?

mysql sql sql-update aggregate-functions
Cory picture Cory · Oct 5, 2010 · Viewed 103.6k times · Source

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.

Answer

OMG Ponies picture OMG Ponies · Oct 5, 2010

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.

Related questions

Multiple Updates in MySQL

I know that you can insert multiple rows at once, is there a way to update multiple rows at once (as in, in one query) in MySQL? Edit: For example I have the following Name id Col1 Col2 Row1 1 6 1 Row2 2 2 3 …

Read More
mysql update column with value from another table

I have two tables, both looking like id name value =================== 1 Joe 22 2 Derk 30 I need to copy the value of value from tableA to tableB based on check name in each table. Any tips for this UPDATE statement?

Read More
MySQL - UPDATE multiple rows with different values in one query

I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand. For instance, three updates into 1 query: UPDATE table_users …

Read More