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 (retry)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

134 Posts

Posted - 11/20/2013 :  13:45:17  Show Profile  Reply with Quote
The query below brings up a set of data below that. All items are valued at $9.99. If a row has any information in the column "ShadowOf", I want to update that row. It should take the price from the row where the contents of shadowof appear & add 2%. To give an example 'GreenApples' is a shadow of 'BoxOfApples'. 'BoxOfApples' is valued at $9.99, so I want to make 'GreenApples' $10.19. How would I do this?

I've attempted with a similar query, but the problem with my previous query is that it only affects the first Shadow. Each additional shadow is ignored. [url=http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=189733]That post can be found here[/url] Thanks!

DECLARE @tmpTable TABLE(ID NVARCHAR(50), BuyDotComPrice DECIMAL(18,2), ShadowOf NVARCHAR(50), CompanyID INT);
INSERT INTO @tmpTable VALUES
('BoxOfApples', 9.99, '', 344),
('GreenApples', 9.99, 'BoxOfApples', 344),
('GalaApples', 9.99, 'BoxOfApples', 344),
('CaseOfSoda', 9.99, '12Sodas', 344),
('12Sodas', 9.99, '', 344),
('PackageOfSoda', 9.99, '12Sodas', 344);

SELECT ID,BuyDotComPrice, ShadowOf, CompanyID FROM @tmpTable


ID	BuyDotComPrice	ShadowOf	CompanyID
BoxOfApples	9.99		344
GreenApples	9.99	BoxOfApples	344
GalaApples	9.99	BoxOfApples	344
CaseOfSoda	9.99	12Sodas	344
12Sodas	9.99		344
PackageOfSoda	9.99	12Sodas	344


-Sergio
I use Microsoft SQL 2008

Lamprey
Flowing Fount of Yak Knowledge

4344 Posts

Posted - 11/20/2013 :  13:57:09  Show Profile  Reply with Quote
Here are two ways. Straight update and a CTE version that might hlpe brake down the problem:
UPDATE
	Child
SET
	BuyDotComPrice = (Parent.BuyDotComPrice * 1.02)
FROM
	@tmpTable AS Parent
INNER JOIN
	@tmpTable AS Child
	ON Parent.ID = Child.ShadowOf

CTE:
;WITH Parent AS
(
	SELECT
		(A.BuyDotComPrice * 1.02) AS NewPrice
		,A.ID
	FROM
		@tmpTable AS A
	WHERE ShadowOf <= ''
)
,Child AS
(
	SELECT *
	FROM @tmpTable
	WHERE ShadowOf > ''
)

UPDATE
	Child
SET
	BuyDotComPrice = Parent.NewPrice
FROM
	Parent
INNER JOIN
	Child
	ON Parent.ID = Child.ShadowOf

Edited by - Lamprey on 11/20/2013 13:59:41
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3322 Posts

Posted - 11/20/2013 :  13:58:55  Show Profile  Reply with Quote
SELECT
	*,
	COALESCE(b.NewBuyDotComPrice,a.BuyDotComPrice) AS NewPrice
FROM
	@tmpTable a
	OUTER APPLY
	(
		SELECT BuyDotComPrice * 1.02 AS NewBuyDotComPrice
		FROM @tmpTable b
		WHERE b.Id = a.ShadowOf
	) b
Go to Top of Page

SergioM
Posting Yak Master

134 Posts

Posted - 11/20/2013 :  15:38:54  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

Here are two ways. Straight update and a CTE version that might hlpe brake down the problem:
UPDATE
	Child
SET
	BuyDotComPrice = (Parent.BuyDotComPrice * 1.02)
FROM
	@tmpTable AS Parent
INNER JOIN
	@tmpTable AS Child
	ON Parent.ID = Child.ShadowOf

CTE:
;WITH Parent AS
(
	SELECT
		(A.BuyDotComPrice * 1.02) AS NewPrice
		,A.ID
	FROM
		@tmpTable AS A
	WHERE ShadowOf <= ''
)
,Child AS
(
	SELECT *
	FROM @tmpTable
	WHERE ShadowOf > ''
)

UPDATE
	Child
SET
	BuyDotComPrice = Parent.NewPrice
FROM
	Parent
INNER JOIN
	Child
	ON Parent.ID = Child.ShadowOf



WOOOOOOOOOTTT! Thanks! I'm still wrapping my mind around it. CTE is confusing for me. But it works!! Thanks

-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.11 seconds. Powered By: Snitz Forums 2000