Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update one row based on another.

Author  Topic 

SergioM
Posting Yak Master

170 Posts

Posted - 2013-11-18 : 11:54:35
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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-18 : 13:05:49
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

170 Posts

Posted - 2013-11-18 : 17:06:24
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
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-18 : 17:27:49
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

170 Posts

Posted - 2013-11-20 : 11:05:22
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-20 : 12:04:35
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

170 Posts

Posted - 2013-11-20 : 13:30:58
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
   

- Advertisement -