SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update one row based on another.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

144 Posts

Posted - 11/18/2013 :  11:54:35  Show Profile  Reply with Quote
I need to update the price of one row based on another. In this case it takes the price from a sku which has a value in the "ShadowOf" field. ShadowOf is a parent sku. It takes the parent sku price, adds 2% and applies it to the child sku. It's all working well except I'm not sure how to handle it when a parent has more than one child. It updates the first & sets the rest to null. How do I fix this?

Enclosed is the sample data + query. Thanks!

DECLARE @tmpTable TABLE(ID NVARCHAR(50), BuyDotComPrice DECIMAL(18,2), ShadowOf NVARCHAR(50), CompanyID INT);
INSERT INTO @tmpTable VALUES
('ALPINE SWR823D', 99.99, '', 344),
('APPLE IPAD2GN16GBWFWRB', 99.99, '', 344),
('APPLE TOUCH8GB4GNW KIT', 99.99, '', 344),
('BRAUN 3170NU DD2', 99.99, 'BRAUN 3170', 344),
('BRAUN SE7681', 99.99, '', 344),
('CAMC STARTERKIT_4', 99.99, '', 344),
('CAMC STARTERKIT_5', 99.99, '', 344),
('CAMC STARTERKIT_6', 99.99, '', 344),
('CAMC STARTERKIT_7', 99.99, '', 344),
('CANON 60D/18-13517B32GB', 99.99, '', 344),
('CANON 7DKIT/18-135', 99.99, '', 344),
('CANON REBELT2I/18-5520B16GB', 99.99, '', 344),
('CANON REBELT3I/18-55K20B8GB', 99.99, '', 344),
('CANON REBELT3I/18-55K24B24GB', 99.99, '', 344),
('CHELCO STR550', 99.99, '', 344),
('CHELCO TR400D DD', 99.99, 'CHELCO TR400D', 344),
('IHome IHM10S', 99.99, '', 344),
('iHome iHM16KRB', 99.99, '', 344),
('IHOME IHM9SRB', 99.99, '', 344),
('KODAK SV811RB-S1', 99.99, 'KODAK SV811RB', 344),
('KODAK SV811RB', 99.99, '', 344),
('KODAK Z9906B16GB', 99.99, '', 344),
('KODAK Z9906B4GB', 99.99, '', 344),
('OLYM ESHOULDERBAG', 99.99, '', 344),
('PANA SD2GB', 99.99, '', 344),
('PANA SD2GBKIT', 99.99, '', 344),
('SAMSUNG PL170S', 99.99, '', 344),
('SD16GBKIT', 99.99, '', 344),
('SHEET GRIPPERS', 99.99, '', 344),
('SLR STARTERKIT CA58A0', 99.99, 'SLR STARTERKIT CA58A1', 344),
('TECH RPDH1200S', 99.99, '', 344),
('TIMEX T435K', 99.99, '', 344),
('TIMEX T436KRB', 99.99, '', 344),
('TRI 50INCH DD2', 99.99, 'TRI 50INCH', 344),
('TRI 50INCH DD3', 99.99, 'TRI 50INCH', 344),
('VIBE 90DGHDMIADAPTER 5P', 99.99, '', 344),
('VIBE DJ750 DD', 99.99, 'VIBE DJ750', 344),
('VIBE DJ750 DD3', 99.99, 'VIBE DJ750', 344),
('VIBE VA09', 99.99, '', 344),
('VIBE VA09-2P', 99.99, '', 344),
('VIBE VA09a', 99.99, 'VIBE VA09', 344),
('VIBE VAU7002', 99.99, '', 344),
('VIBE VAU7002 DD', 99.99, 'VIBE VAU7002', 344),
('VIBE VC-105', 99.99, '', 344),
('VIBE VC-105 2P DD', 99.99, 'VIBE VC-105 2PK', 344),
('VIBE VE-202', 99.99, '', 344),
('VIBE VE-3802PK DD', 99.99, 'VIBE VE-380 2PK', 344),
('ZEIKOS TTT', 99.99, '', 344);

DECLARE @vPercentagePrice DECIMAL(18,2)
		,@vCompanyID INT
SET		@vPercentagePrice='1.02'
SET		@vCompanyID='344'

; WITH CTE AS
(SELECT ROW_NUMBER() OVER(PARTITION BY LEFT(ID, 11) ORDER BY ID) AS RN, * FROM @tmpTable WHERE LEN(ShadowOf)>0 AND ShadowOf IS NOT NULL AND CompanyID=@vCompanyID)
UPDATE P
SET [BuyDotComPrice]=(SELECT (CEILING([BuyDotComPrice]*@vPercentagePrice)+.99) FROM CTE WHERE LEFT(ID, 11) = LEFT(P.ID, 11) and RN = 2)
FROM @tmpTable P INNER JOIN CTE T ON T.ID = P.ID and T.RN = 1; 
SELECT ID,BuyDotComPrice, ShadowOf, CompanyID FROM @tmpTable


-Sergio
I use Microsoft SQL 2008

Edited by - SergioM on 11/18/2013 17:01:20

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 11/18/2013 :  13:05:49  Show Profile  Reply with Quote
Do you define the parent as rows that have a ShadowOf value? How do you determine which rows are a child of which parent?
Go to Top of Page

SergioM
Posting Yak Master

144 Posts

Posted - 11/18/2013 :  17:06:24  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

Do you define the parent as rows that have a ShadowOf value? How do you determine which rows are a child of which parent?



If the Length of ShadowOf is greater than 0, then this row is a child SKU. The parent SKU is the text which is written in the ShadowOf field. In the example below BRAUN 3170NU DD2 is the child SKU of BRAUN 3170

ALPINE SWR823D	99.99		344
APPLE IPAD2GN16GBWFWRB	99.99		344
APPLE TOUCH8GB4GNW KIT	99.99		344
BRAUN 3170NU DD2	NULL	BRAUN 3170	344
BRAUN SE7681	99.99		344
CAMC STARTERKIT_4	99.99		344
CAMC STARTERKIT_5	99.99		344
CAMC STARTERKIT_6	99.99		344
CAMC STARTERKIT_7	99.99		344


-Sergio
I use Microsoft SQL 2008

Edited by - SergioM on 11/18/2013 17:07:05
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 11/18/2013 :  17:27:49  Show Profile  Reply with Quote
Unfortunately, I still don't understand your data. Given the sample data you provided, if the update was run against that dataset and you selected the table, what do you want the output to be?
Go to Top of Page

SergioM
Posting Yak Master

144 Posts

Posted - 11/20/2013 :  11:05:22  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

Unfortunately, I still don't understand your data. Given the sample data you provided, if the update was run against that dataset and you selected the table, what do you want the output to be?


I see, I was probably unclear. To take a real world example, TRI 50INCH DD2 & TRI 50INCH DD3 are children of TRI 50INCH. However You'll notice that only the first child TRI 50INCH DD2 receives an update based on the 2% addition. The second child TRI 50INCH DD3 is not updated.

ID	BuyDotComPrice	ShadowOf	CompanyID
TRI 50INCH DD2	103.00	TRI 50INCH	344
TRI 50INCH DD3	99.99	TRI 50INCH	344


-Sergio
I use Microsoft SQL 2008
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 11/20/2013 :  12:04:35  Show Profile  Reply with Quote
I'm not sure if starting over is needed, but I'm reading things that don't seem to align logically. In your original post you said:
quote:
this case it takes the price from a sku which has a value in the "ShadowOf" field. ShadowOf is a parent sku. It takes the parent sku price, adds 2% and applies it to the child sku.
In your sample above, where is the sku price for "TRI 50INCH"?

Assuming that is still the correct logic, I still don't see how to make apply any rules to your sample data to achieve that. Is it really just adding 2% to any Price that has a value in the ShadowOf column?
Go to Top of Page

SergioM
Posting Yak Master

144 Posts

Posted - 11/20/2013 :  13:30:58  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

I'm not sure if starting over is needed, but I'm reading things that don't seem to align logically. In your original post you said:
quote:
this case it takes the price from a sku which has a value in the "ShadowOf" field. ShadowOf is a parent sku. It takes the parent sku price, adds 2% and applies it to the child sku.
In your sample above, where is the sku price for "TRI 50INCH"?

Assuming that is still the correct logic, I still don't see how to make apply any rules to your sample data to achieve that. Is it really just adding 2% to any Price that has a value in the ShadowOf column?


You are right, it's necessary to start over. I included an excerpt from the DB, but was not careful to make sure that all necessary parts were present... Thanks for the help either way.

-Sergio
I use Microsoft SQL 2008
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000