Menu
I have the following tables set up:
Articles: ID | TITLE | CONTENT | USER | NUM_COMMENTS COMMENTS ID | ARTICLE_ID | TEXT I need a sql statement which updates the NUM_Comments field of the articles table with teh count of the comments made against the article like:
update articles a, comments f set a.num_comments = COUNT(f.`id`) where f.article_id = a.id The sql above doesn't work and I get an Invalid Use fo Group function error. I'm using MySQL Here.
697
UPDATE table SET col = new_value WHERE col = old_value AND other_col = some_other_value; UPDATE table SET col = new_value WHERE col = old_value OR other_col = some_other_value; As you can see, you can expand the WHERE clause as much as you'd like in order to filter down the rows for updating to what you need.
To update multiple columns use the SET clause to specify additional columns. Just like with the single columns you specify a column and its new value, then another set of column and values. In this case each column is separated with a column.
To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT . When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.
You can't have a join in an update statement. It should be
update articles set num_comments = (select count (*) from comments where comments.article_id = articles.id) This will update the entire articles table, which may not be what you want. If you intend to update only one article then add a 'where' clause after the subquery.
147
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
