Update Query Help

[grimmy26]

I have the following Situation:-

TableA
Columns called UniqueNumber and CollectionID

TableB
RefCount and collectionID

In table a there are multiple rows with the same collectionID and what I want to do is to group how many rows have the same collection ID and then however many rows there are I want to update the refcount with this value

I have worked the first bit out as:-
select count(*) as numberofrows, collectionid from tablea
group by collectionid

This gives me each collectionid and the amount of rows that refer to this collection id.

I now want to take the values out of each of the numberofrows column and update the RefCount row in TableB with that value and this is the bit that I am stuggling with.

I hope I have made some sort of sense with my question.


MCSE NT4, 2000, 2003

 

[gmmastros]

Try this...

[COLOR=blue]Update[/color] TableB
[COLOR=blue]Set[/color]    RefCount = A.numberofrows
[COLOR=blue]From[/color]   TableB
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
         [COLOR=blue]select[/color] [COLOR=#FF00FF]count[/color](*) [COLOR=blue]as[/color] numberofrows, 
                collectionid 
         [COLOR=blue]from[/color]   tablea
         [COLOR=blue]group[/color] [COLOR=blue]by[/color] collectionid
         ) [COLOR=blue]As[/color] A
         [COLOR=blue]On[/color] TableB.CollectionId = A.CollectionId

Essentially, you take the query you already wrote, and make it a derived table that you join to TableB.

If anything about this doesn't make sense, please let me know and I will explain further.

-George

"the screen with the little boxes in the window." - Moron

 

[bborissov]

UPDATE TableB SET RefCount = ISNULL(Tbl1.Cnt,0)
FROM TableB
LEFT JOIN (SELECT collectionid,
                  count(*) as Cnt
           from tablea
           group by collectionid) Tbl1
ON TableB.collectionid = Tbl1.collectionid

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP