Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Problem Expand / Collapse
Author
Message
Posted Monday, March 15, 2010 8:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, March 15, 2010 9:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:02 PM
Points: 199, Visits: 602
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
Posted Monday, March 15, 2010 9:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Posted Tuesday, March 16, 2010 5:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, March 16, 2010 6:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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 »

Add to briefcase

Permissions Expand / Collapse