Thx guys,first problem solved but Things got a little more complicated. I want to include a case when clause with the sum function in the Set statement as follows (disregard the field names):

UPDATE p

SET p.Price =(CASE WHEN SRND01 <> 0 AND Month('2010.01.28') = 1 THEN SUM(SRBP01)/SUM(SRND01)*C8SPT/C8PWD END )

FROM(SELECT ROW_NUMBER() OVER(PARTITION BY Product_Name ORDER BY Qty) AS Seq,Price,Product_Name FROM #Products)p

JOIN #Price pr

ON pr.Product_Name = p.Product_Name

AND p.Seq=1

But I am having an error when trying to run it. Any help please?