MYSQL updating data from other table

Question

Hi,

I have two tables like of this structure

content (content_id, content_type, user_id, time, comment_count)

comments (comment_id, content_id, userid, comment, comment_time)

What I wold like to do is update the comments_count field with sum of comments i.e COUNT(content_id) from the comments table.

I am not able to figure out the right syntax

Thanks

Answer 1

Cross Table Update with MySQLCross Table Update with MySQL

UPDATE product p, productPrice pp
SET pp.price = pp.price * 0.8
WHERE p.productId = pp.productId
AND p.dateCreated < '2004-01-01'

Answer 2

UPDATE content c1 SET comment_count=(
    SELECT COUNT(c2.content_id) FROM comments c2 
    WHERE c1.content_id = c2.content_id
)