stored procedure update query

[tobyzak99 via AccessMonster.com]

Hi

In a stored procedure I am currently updating a table by inserting a
calculation into a temporary table and then updating another table with the
values from the temp table.

Is there a way to update the table in one hit without using the temp table

SELECT CASE SUM(TotalQuantity) WHEN 0 THEN 0 ELSE SUM(TotalCost) / SUM
(TotalQuantity) END AS AveragePrice
Into #TempTable
FROM BudgetTable
GROUP BY BudgetYear, Account



UPDATE OtherTable
SET OtherTable.AveragePrice = #TempTable.AveragePrice
FROM #TempTable
JOIN OtherTable
ON #TempTable.BudgetYear = OtherTable.BudgetYear AND
#TempTable.Account = OtherTable.Account


Thanks - Toby

 

[Sylvain Lafontaine]

I would say to use a subquery in place of the temptable. However, your
current example doesn't make any sense because the created temptable has
only one column.

Also, for this kind of question, a newsgroup such a
microsoft.public.sqlserver.programmation would be more appropriate but I'm
not sure if you can access from AccessMonster as it makes a pretty good job
of hiding where exactly you are posting your question.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[tobyzak99 via AccessMonster.com]

Hi Sylvian

Yes sorry the example doesn't make sense.

I thought a subquery might work but am not sure how to write it. If you don't
mind, would you show me how to rewrite using a subquery.

SELECT CASE SUM(TotalQuantity) WHEN 0 THEN 0 ELSE SUM(TotalCost) / SUM
(TotalQuantity) END AS AveragePrice, BudgetYear, Account
Into #TempTable
FROM BudgetTable
GROUP BY BudgetYear, Account

UPDATE OtherTable
SET OtherTable.AveragePrice = #TempTable.AveragePrice
FROM #TempTable
JOIN OtherTable
ON #TempTable.BudgetYear = OtherTable.BudgetYear AND
#TempTable.Account = OtherTable.Account

Thanks - Toby

 

[Bob McClellan]

Something like this should work Toby...

UPDATE OtherTable

SET OtherTable.AveragePrice = tt.AveragePrice

FROM (SELECT AveragePrice = CASE SUM(TotalQuantity) WHEN 0 THEN 0 ELSE
SUM(TotalCost) / SUM
(TotalQuantity) END,
BudgetYear,
Account
FROM BudgetTable
GROUP BY BudgetYear, Account) tt

INNER JOIN OtherTable ON tt.BudgetYear = OtherTable.BudgetYear
AND tt.Account =
OtherTable.Account


hth,
...bob