update using sum of fields - SQL Server Forums

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 using sum of fields

Author

Topic

amlwwalker
Starting Member

2 Posts

Posted - 2012-05-10 : 16:00:22

Hi Im (quite) new to sql programming, and im trying to write what I think should be quite a simple query.
I have a table with groups of entries - lap times for consecutive laps for runners, for example:
name time
alex 3.25
alex 3.28
alex 3.31
tim 3.21
tim 3.30
tim 3.25

etc etc - its quite a large dataset. what i want to do is have another table, that merged them together so that for each runner i had one entry with the average time:
alex 3.28
tim 3.253

my tables are called temp - which stores the orignial data, and temp2 will store the new data.
i tried to run something like this:
update temp2 set temp2.time=(select sum(temp.time)/3 from temp group by name) where temp2.name = temp.name

but that tells me it cant find temp.name
Is there a better way to do this?
Thanks everyone
P.S Im using H2 database software which I dont think is that good but cant really change as my laptop is works and I dont have administrator rights....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 16:08:50

[code]
update t2
set t2.time= t1.avgtime
from temp2 t2
inner join
(select name,avg(time) as avgtime
from temp
group by name)t1
on t1.name = t2.name
[/code]

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

amlwwalker
Starting Member

2 Posts

Posted - 2012-05-10 : 16:21:44

Thanks visakh16, but I dont quite understand:
is t2 temp2 and t1 temp?

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-10 : 16:55:47

quote:
Originally posted by amlwwalker

Thanks visakh16, but I dont quite understand:
is t2 temp2 and t1 temp?


T2 is a temp2 table here and t1 is derived table name that is 

(select name,avg(time) as avgtime 
 from temp 
 group by name)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 19:58:30

quote:
Originally posted by amlwwalker

Thanks visakh16, but I dont quite understand:
is t2 temp2 and t1 temp?

they're table aliases

ie shortnames given for Temp2 and derived table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources