April 13, 2009 at 11:30 am
Ok folks, you sql experts. I have a query below that returns two columns. I want to treat the resultset as a table and update product (rr is a field in product) joining on productid.
update Product set rr = rate where productid = ProductTempRR.ProductID
SELECT ProductTempRR.ProductID, SUM(ProductTempRR.PolkTotal * ProductReplacementSchedule.Replacement / 100) AS rate
FROM ProductTempRR INNER JOIN
ProductReplacementSchedule ON ProductTempRR.Age = ProductReplacementSchedule.Yr AND
ProductTempRR.Category = ProductReplacementSchedule.Category
GROUP BY ProductTempRR.ProductID
April 13, 2009 at 11:49 am
This seems to work with a temp table:
create table #tempR
(
productid int,
rate decimal(16,2)
)
insert into #tempR (ProductID,rate)
SELECT ProductTempRR.ProductID, SUM(ProductTempRR.PolkTotal * ProductReplacementSchedule.Replacement / 100) AS rate
FROM ProductTempRR INNER JOIN
ProductReplacementSchedule ON ProductTempRR.Age = ProductReplacementSchedule.Yr AND
ProductTempRR.Category = ProductReplacementSchedule.Category
GROUP BY ProductTempRR.ProductID
update Product set rr = rate from product inner join
#tempR on Product.ProductID = #tempR.productid
April 13, 2009 at 2:59 pm
Hi foxjazz
A temp table is a valid solution. You can also use a sub-select to avoid the overhead with the temp table.
Try this sample:
DECLARE @t TABLE
(
ProductId INT,
AnyTotal FLOAT,
Replacement FLOAT,
rr FLOAT
)
INSERT INTO @t
SELECT 1, 123.45, 40, NULL
UNION ALL SELECT 1, 234.56, 80, NULL
UNION ALL SELECT 2, 333.44, 100, NULL
UPDATE t1 SET t1.rr = t2.rr
FROM @t t1
JOIN (SELECT ProductId, SUM(AnyTotal * Replacement / 100) rr
FROM @t
GROUP BY ProductId) t2
ON t1.ProductId = t2.ProductId
SELECT * FROM @t
Greets
Flo
April 13, 2009 at 3:15 pm
[font="Verdana"]Also okay in SQL Server 2005 to use a CTE (common table expression), which I find makes the resulting code a little more readable, but is otherwise pretty much the same as a sub-select.
with
CalculateRR as (
select ProductID,
sum(AnyTotal * Replacement) / 100 as rr
from @t
group by
ProductID
)
update t
set t.rr = CalculateRR.rr
from @t t
inner join
CalculateRR
on t.ProductID = CalculateRR.ProductID;
[/font]
April 13, 2009 at 3:45 pm
I thought about the CTE while typing the sub-query. I just determined that, in this case, the sub-query is simple enough that it stays readable.
Greets
Flo
April 13, 2009 at 4:02 pm
Florian Reischl (4/13/2009)
I thought about the CTE while typing the sub-query. I just determined that, in this case, the sub-query is simple enough that it stays readable.Greets
Flo
[font="Verdana"]Agreed in this case (sorry, didn't mean to come across like I was disapproving of your code at all!) I've just gotten into the habit of using them now, and wanted to show the alternative.[/font]
April 13, 2009 at 4:22 pm
Bruce W Cassidy (4/13/2009)
Florian Reischl (4/13/2009)
I thought about the CTE while typing the sub-query. I just determined that, in this case, the sub-query is simple enough that it stays readable.Greets
Flo
[font="Verdana"]Agreed in this case (sorry, didn't mean to come across like I was disapproving of your code at all!) I've just gotten into the habit of using them now, and wanted to show the alternative.[/font]
Nothing to say sorry! I didn't feel disapproved. It was only an explanation why I didn't use it. 😉
Greets
Flo
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply