• Got a Tipila, google, windows live or facebook account ?

This is how to update a column of a table to be the group-by-count of rows, in another table.

User table has a field called postcount which represents the number of posts that a user has made. And the Tip table has all the posts. So we are going to get the number of posts that a user has made from the Tip table and set that value to the User table.

Here's how to do it:

with UserCounts as
(
select count(*) as counts, t.userId as usid from tip t group by  t.userId
)
update [user]   set postcount = UserCounts.counts
from [user]  inner join  UserCounts  on UserCounts.usid = [user].id
1
votes
Login to Tipila