update with a count - how to? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

update with a count - how to?

Author

Topic

fabianus76
Posting Yak Master

191 Posts

Posted - 2006-05-03 : 13:07:38

Hello!

I would like to update a table with some count values from another. How is this possible?

In my case I have a user table (myUsers) and I have a post table (myPosts). I would like to update the TotalPosts row in myUsers.

Thank you very much for any help!

Regards,
Fabian

PS I thought to do it like that, but it does not work, saying that TotalPosts does not accept null value (not all users have posts).

UPDATE
dbo.cs_UserProfile
SET
TotalPosts =

(

SELECT
COUNT(dbo.cs_Posts.PostID) AS FIELD_1
FROM
dbo.cs_Posts
WHERE
UserID = dbo.cs_UserProfile.UserID
GROUP BY
dbo.cs_Posts.UserID

)

my favorit hoster is ASPnix : www.aspnix.com !

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-05-03 : 13:18:27

Use isNull function.

JohnnySQL
Starting Member

3 Posts

Posted - 2006-05-03 : 13:30:10

Just be sure to put the isNull in the right place:

UPDATE
dbo.cs_UserProfile
SET
TotalPosts =
isNull((
SELECT
COUNT(dbo.cs_Posts.PostID) AS FIELD_1
FROM
dbo.cs_Posts
WHERE
UserID = dbo.cs_UserProfile.UserID
GROUP BY
dbo.cs_Posts.UserID
),0)

fabianus76
Posting Yak Master

191 Posts

Posted - 2006-05-03 : 13:34:48

Thank you very much ! That's perfect.

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources