Update Problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Update Problem

Update Problem

Rate Topic

Display Mode

Topic Options

Author

Message

meltannir

Posted Monday, March 15, 2010 8:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 1:20 AM
Points: 5, Visits: 21

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.

Post #882995

Daryl AZ

Posted Monday, March 15, 2010 9:23 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:25 PM
Points: 196, Visits: 560

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

Post #883045

GSquared

Posted Monday, March 15, 2010 9:27 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Post #883053

meltannir

Posted Tuesday, March 16, 2010 5:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 1:20 AM
Points: 5, Visits: 21

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?

Post #883666

GSquared

Posted Tuesday, March 16, 2010 6:37 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Post #883710

« Prev Topic | Next Topic »

Permissions