UPdate table with Count - 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

General SQL Server Forums

New to SQL Server Programming

UPdate table with Count

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-07-23 : 08:02:06

I need to count records in one table and update another table with the count.

This script gives me the correct count I need:
SELECT GroupID, Category, SubCategory, JobLevel, COUNT(*) AS cc
FROM BacoDiscussions
GROUP BY GroupID, Category, SubCategory, JobLevel

Now I need to take the values and update bacodiscussioncount with the value of cc.

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-07-23 : 08:07:25

Forgot to add that the bacodiscussioncount table also has groupID, Category,subcategory,joblevel.

It holds the record count for the groups that are in the bacodiscussions table.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-23 : 08:07:42

update or just insert?

Em

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-07-23 : 08:10:33

update.

aoriju
Posting Yak Master

156 Posts

Posted - 2008-07-23 : 08:12:15

See Vack
You can put into table variable and insert.

quote:
Originally posted by Vack

Forgot to add that the bacodiscussioncount table also has groupID, Category,subcategory,joblevel.

It holds the record count for the groups that are in the bacodiscussions table.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-23 : 08:17:21

why a table variable??

quote:
Originally posted by aoriju

See Vack
You can put into table variable and insert.
quote:
Originally posted by Vack

Forgot to add that the bacodiscussioncount table also has groupID, Category,subcategory,joblevel.

It holds the record count for the groups that are in the bacodiscussions table.

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-07-23 : 08:21:59

I do not know what that means

Table variable?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-23 : 08:23:55

ignore the post from aoriju. you don't need a table variable at all. you just need a join in your update query

Em

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-07-23 : 08:27:07

That's where I'm not sure how to make the join since I'm grouping.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 10:02:37

UPDATE t
SET t.countfield=tmp.cc
FROm Yourtable t
INNER JOIN (SELECT GroupID, Category, SubCategory, JobLevel, COUNT(*) AS cc
FROM BacoDiscussions
GROUP BY GroupID, Category, SubCategory, JobLevel)tmp
ON tmp.fields=t.fields

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources