how to update a sum value from join tables?

[Eunice VB junior]

Hi, I wrote this update query. But it doesn't work. The error message : An aggregate may not appear in the set list of an UPDATE statement. Here is my query. update udtStudent set score=sum(b.studentscore) from udtStudent a inner join udtQuestionHistory b on a.testid=b.testid and a.empno=b.empno where a.score is null group by b.empno,b.testid i wish to update the score = sum of studentscore from udtQuestionHistory the problem is at sum().Can anyone advice me how should i overcome it? Thanks in advance. thanks.

[Joe 2]

Eunice (VB junior) wrote:

set score=sum(b.studentscore) from udtStudent a inner join udtQuestionHistory b on a.testid=b.testid and a.empno=b.empno where a.score is null group by b.empno,b.testid

Try to bring the sum in a select clause and assign it to an integer variable. For instance, Declare @intSum INT select @intSum = sum(score) from student a inner join history b on a.testid = b.testid Now, @intSum will hold the sum of studentscore from history table. Later, proceed with your update statement like update student set score = @intSum where ##your condition## Hope that thought might help you.

[pmarfleet]

You've already asked this question once. Please don't double-post.

Paul Marfleet

[Eunice VB junior]

Hi CS, thanks for the guidance. I got what you meant. Here is what i did according to your advice and it works. Declare @intSum INT declare @testID int declare @empno int select @intSum = sum(studentscore),@testid=testid,@empno=empno from udtquestionhistory group by empno,testid,questype,testtype,partnumber,process update udtstudent set score=@intSum where testid=@testid and empno=@empno Thanks for the help.

[Eunice VB junior]

Hi pmarfleet, Actually there are different questions. The 2nd question is about subselect. But i'm using the same table as my query testing. Anyway, thanks for the input.