quote:

---

Originally posted by mtannir50

I have 2 tables #Products and #PrICE:

CREATE TABLE #Products
(
Product_Name VARCHAR(20),
Qty INT,
Price DECIMAL(10,2)
)

CREATE TABLE #PrICE
(
Product_Name VARCHAR(20),
Pr DECIMAL(10,2)
)
INSERT INTO #PrICE
SELECT 'Prod1',2
INSERT INTO #PrICE
SELECT 'Prod2',4

INSERT INTO #Products
SELECT 'Prod1',2,2.5
UNION
SELECT 'Prod2',3,6.5
UNION
SELECT 'Prod1',3,2.5

I want to update only the first row (or one row for each product)in the #Products table for each product name.The following command updates all the records.

Update #Products
SET Price = PR
FROM
(#Products
INNER JOIN #PRICE ON #Products.Product_Name = #PrICE.Product_Name )

Help please.

---

quote:

---

Originally posted by mtannir50

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?

---