SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 update table with count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dzabor
Posting Yak Master

USA
132 Posts

Posted - 04/29/2013 :  18:31:14  Show Profile  Send dzabor an AOL message  Reply with Quote
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.

Edited by - dzabor on 04/30/2013 09:27:39

James K
Flowing Fount of Yak Knowledge

3643 Posts

Posted - 04/29/2013 :  18:35:45  Show Profile  Reply with Quote
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

USA
132 Posts

Posted - 04/29/2013 :  18:51:57  Show Profile  Send dzabor an AOL message  Reply with Quote
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

USA
132 Posts

Posted - 04/29/2013 :  19:26:43  Show Profile  Send dzabor an AOL message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000