Update one row based on another (retry)

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 (retry)

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2013-11-20 : 13:45:17

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-20 : 13:57:09

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-20 : 13:58:55

[code]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[/code]

SergioM
Posting Yak Master

170 Posts

Posted - 2013-11-20 : 15:38:54

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources