SQL: Counting Problem relating to two tables

Question

I have a table with columns : Sku, Count

in another table there are sku's with different images, so in this table one sku is repeated several times with multiple file extensions, what i need to do is to count the number of images that a sku has in the second table and then update the count column in the first table with that number. I would greatly appreciate any help.

UPDATE
  MediaResource    
SET
  [mrCount] = 
(
SELECT
 Count(PartNumber)
)....I don't know how to handle this

Answer 1

try something like this:

DECLARE @MediaResurce      table (sku varchar(3), CountOf int)
DECLARE @MediaResurce_Pics table (sku varchar(3), ImageName varchar(10))

INSERT @MediaResurce VALUES ('abc',0)
INSERT @MediaResurce VALUES ('mno',0)
INSERT @MediaResurce VALUES ('xyz',0)

INSERT @MediaResurce_Pics VALUES ('abc','a.jpg')
INSERT @MediaResurce_Pics VALUES ('abc','a.gif')
INSERT @MediaResurce_Pics VALUES ('xyz','a.gif')

UPDATE a
    SET CountOf=dt.CountOf
    FROM @MediaResurce  a
        INNER JOIN (SELECT 
                        aa.Sku,ISNULL(COUNT(bb.sku),0) AS CountOf
                        FROM @MediaResurce                      aa
                            LEFT OUTER JOIN @MediaResurce_Pics  bb ON aa.sku=bb.sku
                        GROUP BY aa.Sku
                   ) dt ON a.sku=dt.sku

SELECT * FROM @MediaResurce

OUTPUT:

sku  CountOf
---- -----------
abc  2
mno  0
xyz  1

(3 row(s) affected)

Note:
If you only need your equivalent @MediaResurce table for a count of the @MediaResurce_Pics table, then I would strongly consider replacing your equivalent @MediaResurce table with a view. It will automatically keep in sync without needing a trigger. If you actually need your equivalent @MediaResurce table for other column, still consider a view for the Count column. You can materialize the column if you need for extra speed during selects.

Answer 2

Update MediaResource
Set mrCount =   (
                Select Count(*)
                From TheOtherTable As T2
                Where T2.SKU = MediaResource.SKU
                )