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 

dzabor
Posting Yak Master

138 Posts

Posted - 2013-04-29 : 18:31:14
I am trying to update a table based on a count of company records:

select Top 100 co_sort, count (*) as count
from Institute II
inner join Name n
on n.ID = ii.id
group by co_sort
order by count desc

Above query returns:
ABC CO 9699
Ernst CO 7532
Coopers 7463
KPMGYO 7022
IBMB 1865


I would like to update the CO_Record_Count in my existing table 'Company' (below):ID, CO_Sort, CO_Record_Count, CO_Rank


update c
set CO_Record_Count = count
from company c


I can use CO_Sort to join. It will udpate each ID with the company count. I am also trying to rank the company, but that is not as urgent.


Thanks for any help you can provide.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-29 : 18:35:45
Combining the two queries you posted as shown below. If you remove the TOP 100, be sure to also remove the ORDER BY clause
WITH    cte AS
( SELECT TOP 100
co_sort ,
COUNT(*) AS count
FROM ISACA_Institute II
INNER JOIN Name n ON n.ID = ii.id
GROUP BY co_sort
ORDER BY count DESC
)
UPDATE c
SET CO_Record_Count = count
FROM company c
INNER JOIN cte t ON t.CO_Sort = c.CO_Sort;
Go to Top of Page

dzabor
Posting Yak Master

138 Posts

Posted - 2013-04-29 : 18:51:57
Thanks - I will try. I was just playing with a temp table and got it to work, but will see what this does - seems better. Do you know anything about ranking? I need to rank the top 100 and add to the records.
Go to Top of Page

dzabor
Posting Yak Master

138 Posts

Posted - 2013-04-29 : 19:26:43
I actually got this far: select top 100 co_record_count, RANK ( ) OVER ( order by co_record_count desc) as rank from CO_Top100

Now I need to find a way to update my table as below:

update c
set CO_Rank = Rank
from CO_Top100
Go to Top of Page
   

- Advertisement -