Looping Through a Result Set. - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Looping Through a Result Set.

Author

Topic

davidshq
Posting Yak Master

119 Posts

Posted - 2005-12-03 : 12:36:07

I want to create a job that averages the results from one table and then inserts them into another table. I've got the average command down fine:
update game set Popularity =
(select avg(Popularity) from GamePopularity Where gID=2)
Where ID=2
As you can see it inserts and averages, the problem is I have say three hundred records in this table right now, so I really need a While loop that cycles through each record, rather than me manually entering the ID like I did above.
(Pseudo-Code)
select ID from game
For Each ID in Game
update game set Popularity =
(select avg(Popularity) from GamePopularity Where gID=CurrentID)
Where ID=CurrentID
Next
David

- http://www.gamesecretary.com/
- http://www.thehungersite.com/
- http://www.grid.org/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-12-03 : 12:41:25

No need for a loop at all:

UPDATE A SET Popularity=B.Avg_Popularity
FROM Game A INNER JOIN (SELECT ID, Avg(Popularity) Avg_Popularity FROM GamePopularity GROUP BY ID) B
ON A.ID=B.ID

davidshq
Posting Yak Master

119 Posts

Posted - 2005-12-03 : 13:03:08

Thanks. I have one question. My data looks something like this:
Game Table:
ID - Title - Popularity
1 - Kickball - 8
2 - Dodgeball - 9
3 - Fooseball - 5
GamePopularity Table:
ID - uID - gID - Popularity
1 - JamesG - 2 - 4
2 - WilliamC - 1 - 8
3 - MarthaE - 1 - 10
As you can see, just b/c the ID in GamePopularity is the same as the ID in Game doesn't make the two about the same Game. Rather, this will generally not be true. Rather gID in GamePopularity is equal to the referenced Game in the Game table.
In your example code you said to GROUP BY ID and to JOIN ON ID...But don't I want them to GROUP BY gID and JOIN ON A.ID=B.gID?
Thanks again for your help.
David.

- http://www.gamesecretary.com/
- http://www.thehungersite.com/
- http://www.grid.org/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-12-03 : 13:21:35

Yes, that's correct.

davidshq
Posting Yak Master

119 Posts

Posted - 2005-12-03 : 13:45:40

Thanks. :)

- http://www.gamesecretary.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources