Update a rows in one table for each row in other table

Hello all, i am a newbie in SQL and i want to ask for your help in order to do the following update statement.

I have a table tblUsers which holds all the users, one of the fields in their GroupId. In the UserGroup table i have a field "UsersCount" and in that field i want to keep the number of the users in that group. Basically i want everytime i am saving a user to recalculate the users in all usergroups.

I am trying to have something like:
SELECT User_GroupId, count(User_GroupId) as UsersCount FROM tblUsers
WHERE DeletedFlag = 0
GROUP BY User_GroupId

and then:
"FOR EACH" User_GroupId
UPDATE tblUserGroups
SET UserGroup_UserCount = UsersCount
WHERE UserGroup_Id = User_GroupId

Thanks a lot.

0
MikeMinsk
8/15/2006 2:58:24 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
1030 Views

Similar Articles

[PageSpeed] 28

Try this oneSmile:

UPDATE tblUserGroups
SET UserGroup_UserCount = UsersCount
FROM tblUsersGroups t join
(SELECT User_GroupId, count(User_GroupId) as UsersCount
FROM tblUsers
WHERE DeletedFlag = 0
GROUP BY User_GroupId) As tmp
ON t.User_GroupId=tmp.User_GroupId


Welcome to my SQL/ASPNET forum for Chinese
http://51up.org/bbs/forumdisplay.php?fid=38
0
Iori_Jay
8/16/2006 8:14:08 AM
Iori_Jay:

Try this oneSmile:

UPDATE tblUserGroups
SET UserGroup_UserCount = UsersCount
FROM tblUsersGroups t join
(SELECT User_GroupId, count(User_GroupId) as UsersCount
FROM tblUsers
WHERE DeletedFlag = 0
GROUP BY User_GroupId) As tmp
ON t.User_GroupId=tmp.User_GroupId

Thank you very much, I will try it very soon!

0
MikeMinsk
8/16/2006 4:44:48 PM
It worked! Thanks a lot!
0
MikeMinsk
8/16/2006 11:07:07 PM
Reply: