Update a column with a COUNT of other fields is SQL?

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.

This should work.

UPDATE articles a SET num_comments = 
(SELECT COUNT(*) FROM comments c WHERE c.article_id = a.id)

But i would rather update only one record when comment has been posted:

UPDATE articles a SET num_comments = 
(SELECT COUNT(*) FROM comments c WHERE c.article_id = 100) WHERE a.id = 100