March 15, 2010 at 8:39 am
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 )
Please help on this issue.
March 15, 2010 at 9:23 am
A method needs to be found to identify which row to update. This method has an ID field that uses MIN. A more common strategy would use a date (or such).
CREATE TABLE #Products
(
ProductsID INT IDENTITY, -- NEW
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
(SELECT MIN(prd.ProductsID) productsID, prd.Product_Name
from #Products prd
GROUP BY prd.Product_Name
) minPrd
INNER JOIN #PRICE ON minPrd.Product_Name = #PrICE.Product_Name
WHERE #Products.productsID = minPrd.productsID
March 15, 2010 at 9:27 am
How are you defining "first row"? There's nothing in the table that indicates the sequence.
If you want to update only one row, and don't care which one, or have some column that indicates which one is "first", then it's easy to set up an update based on a row number. That'll only update one row (or however many you want).
So, which one is "first"?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2010 at 5:29 am
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?
March 16, 2010 at 6:37 am
Turn the aggregation query into either a correlated subquery or a CTE, then use that for your update command.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply