Menu
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.
611
Specific columns can be modified using the SET clause by supplying new values for that column. The WHERE clause can be used to specify the conditions those identify which rows to update. Without using WHERE clause, all rows are updated. The ORDER BY clause is used to update the order that is already specified.
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
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.
147
You can do:
UPDATE categories SET posts = posts + 1 WHERE category_id = 42;
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
