SP Pass Values into another query

[Westond]

SELECT AVG(field1 + field2) as theAverage FROM table WHERE ....

UPDATE Table SET Field = theAverage where ....

Why doesn't this work?

 

[mrdenny]

theAverage is a table. In order to reference another table in an update statement you need to specify that table as part of the from and list the columns that are going to match up. The select statement isn't needed.

update Table
set Field = Avg(theAverage.Field1+theAverage.Field2)
from theAverage
where ...
group by Avg(theAverage.Field1+theAverage.Field2)

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[gmmastros]

You're not using variables properly. Here's how it should be done....

Declare @TheAverage Real

Select @TheAverage = Avg(Field1 + Field2) From Table Where....

Update Table Set Field = @TheAverage Where....

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

You can also do it with a variable.

declare @avg numeric(18,2)
select @avg = avg(field1+field2) from theAverage

update Table
set Field = @avg
where ...

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com