create table Students
(
    StudentID int identity(1,1) primary key,
    Name nvarchar(32) not null,
    TotalScore int not null
);
insert into Students(Name, TotalScore) values('刘德华', 0);
insert into Students(Name, TotalScore) values('张学友', 0);
insert into Students(Name, TotalScore) values('郭富城', 0);


create table CourseScore
(
    StudentID int not null,
    CourseID int not null,
    Score int not null
);
insert into CourseScore values(1 , 1, 80);
insert into CourseScore values(1 , 2, 80);
insert into CourseScore values(1 , 3, 80);
insert into CourseScore values(2 , 2, 95);
insert into CourseScore values(2 , 3, 85);
insert into CourseScore values(3 , 1, 100);
insert into CourseScore values(3 , 2, 75);

--更新
update Students set TotalScore=b.c1
from Students a left join 
(
select StudentID,sum(Score) as c1 
from CourseScore group by StudentID
) b
on a.StudentID=b.StudentID

--查看
select * from Students
/*
StudentID   Name                             TotalScore
----------- -------------------------------- -----------
1           刘德华                              240
2           张学友                              180
3           郭富城                              175
*/

------解决方案--------------------
SQL code
update a set a.TotalScore=b.s
from Students a
join (select StudentID,sum(Score) s from CourseScore group by StudentID) b
on a.StudentID=b.StudentID

select * from Students
/**
StudentID   Name                             TotalScore
----------- -------------------------------- -----------
1           刘德华                              240
2           张学友                              180
3           郭富城                              175

(3 行受影响)
**/

------解决方案--------------------
SQL code
--简化一下
--更新
update a set TotalScore=(select sum(Score) from CourseScore where StudentID=a.StudentID) 
from Students a

------解决方案--------------------
update Students set TotalScore = isnull((select sum(Score) from CourseScore where StudentID = t.StudentID),0) from Students t

------解决方案--------------------
SQL code
create table Students
(
    StudentID int identity(1,1) primary key,
    Name nvarchar(32) not null,
    TotalScore int not null
);
insert into Students(Name, TotalScore) values('刘德华', 0);
insert into Students(Name, TotalScore) values('张学友', 0);
insert into Students(Name, TotalScore) values('郭富城', 0);

create table CourseScore
(
    StudentID int not null,
    CourseID int not null,
    Score int not null
);
insert into CourseScore values(1 , 1, 80);
insert into CourseScore values(1 , 2, 80);
insert into CourseScore values(1 , 3, 80);
insert into CourseScore values(2 , 2, 95);
insert into CourseScore values(2 , 3, 85);
insert into CourseScore values(3 , 1, 100);
insert into CourseScore values(3 , 2, 75);
go

update Students set TotalScore = isnull((select sum(Score) from CourseScore where StudentID = t.StudentID),0) from Students t 

select * from Students
/*

StudentID   Name                             TotalScore  
----------- -------------------------------- ----------- 
1           刘德华                              240
2           张学友                              180
3           郭富城                              175

（所影响的行数为 3 行）
*/

drop table Students, CourseScore