MySQL Update Column +1?

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.

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.

MySQL 8.0 Reference Manual :: 13.2.13 UPDATE Statement, For the single-table syntax, the UPDATE statement updates columns of existing rows in the named table with new values. The SET clause indicates which� 2) Using MySQL UPDATE to modify values in multiple columns. To update values in the multiple columns, you need to specify the assignments in the SET clause. For example, the following statement updates both last name and email columns of employee number 1056:

You can do:

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

Update a column in MySQL, You have to use UPDATE instead of INSERT: UPDATE Syntax. For Example: UPDATE table1 SET col_a='k1', col_b='foo' WHERE key_col='1';� MySQL UPDATE multiple columns . MySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated. The following MySQL statement will update receive_qty, pub

How about:

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

SQL UPDATE Statement, Exercise: Update the City column of all records in the Customers table. Customers City = 'Oslo';. Example - Update multiple columns. Let's look at a MySQL UPDATE example where you might want to update more than one column with a single UPDATE statement. UPDATE customers SET state = 'California', customer_rep = 32 WHERE customer_id > 100; When you wish to update multiple columns, you can do this by separating the column/value pairs with commas.

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

MySQL - UPDATE Query, This will modify any field value of any MySQL table. Syntax. The following code block has a generic SQL syntax of the UPDATE command to modify the data in the� UPDATE Multiple Records. It is the WHERE clause that determines how many records will be updated. The following SQL statement will update the contactname to "Juan" for all records where country is "Mexico":

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

MySQL UPDATE, The MySQL UPDATE statement is used to update columns of existing rows in a table with new values. Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order. If you set a column to the value it currently has, MySQL notices this and does not update it.

MySQL: UPDATE Statement, The MySQL UPDATE statement is used to update existing records in a table in a to update multiple columns, you can do this by separating the column/value� In MySQL, if you want to update a column with the value derived from some other column of the same table we can do so by using a SELF JOIN query and if you wish to modify the value derived from another column like maybe get a substring from the text or break the string using some delimiter, then we can use the SUBSTRING_INDEX function in the query.

MySQL UPDATE Query with Example, UPDATE MySQL command is used to modify rows in a table. The update command can be used to update a single field or multiple fields at the� You can do so by using the SQL UPDATE command. This will modify any field value of any MySQL table. Syntax. The following code block has a generic SQL syntax of the UPDATE command to modify the data in the MySQL table − UPDATE table_name SET field1 = new-value1, field2 = new-value2 [WHERE Clause] You can update one or more field altogether.

MySQL Update Statement Tutorial, MySQL UPDATE Table Command. #1) MySQL Updating Single Column. Now, let's find out a record that� Renaming a Database Column . You rename a column in MySQL using the ALTER TABLE and CHANGE commands together to change an existing column. For example, say the column is currently named Soda, but you decide that Beverage is a more appropriate title. The column is located on the table entitled Menu. Here is an example of how to change it:

Comments
  • 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: The OP wants to store & update the post count related to the category.
  • 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.
  • It's very quick when you have an index on p.post_id.
  • UPDATE CATEGORY SET count = count + 1; perfect solution.
  • Can you provide more context? The question is nearly nine years old, and the same way has been given in answers multiple times
  • I'm using database for 6-7 years. & yesterday I found that solution helpful. So please give positive feedback for my answer.
  • 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?