quote:

---

MichaelAJ writes "I have three tables set up for a contest. Users, Games, and Picks. The Users table stores the point totals, and the UserID. The Picks table stores the GameID,WinnerID, and UserID. The Games table stores the GameID,Winner and the Game value. After scores are enterd and a winner for a game is determined, points must be given to the users who guessed the correct winners. So I wrote the following SQL to return the UserID and their total points:

"Select Picks.UserID,Sum(Games.Points) as TotalPoints From Picks Inner Join Games on (Picks.WinnerID=Games.Winner and Picks.GameID=Games.GameID) Group By Picks.UserID"

From the Results, I need to update the Users table and insert their points. How would I merge this SQL into an Update to make it happen in one pass? I'm running on SQL Server 2000

Thanks for your help"

---

select picks.userID, Sum(Games.Points) as totalPoints
into #foo
From Picks Inner Join Games on (Picks.WinnerID=Games.Winner and Picks.GameID=Games.GameID)
Group By Picks.UserID

update users
set users.points = #foo.totalPoints
from users, #foo
where users.userID = #foo.userID

drop table #foo