Looking for Query

SQL SERVER 2000
I have to tables
Students:
StudentSysID (INT) PK
StudentName (VCHAR 100)
NumTeachers (INT)
Classes:
SysID (INT) PK
StudentSysID (INT)
TeacherID (INT)
I am looking for a query to do the following
UPDATE Students SET NumTeachers = (SELECT DISTINCT COUNT(TeacherID) FROM
Classes WHERE Students.StudentSysID = Classes.StudentSysID)
Which would SEEM give me the number of DIFFERENT teachers each student has,
but its not, it gives me the total number of class records for each student.
What am i doing wrong?
TIA
Tim MorrisonDISTINCT should be inside the COUNT. Try if this works.
UPDATE Students SET NumTeachers = (SELECT COUNT(DISTINCT TeacherID)
FROM Classes WHERE Students.StudentSysID = Classes.StudentSysID)|||Without table structures & sample data for a repro script is hard to
identify the problem. Pl. refer to www.aspfaq.com/5006 to provide required
information for others to better identify the issue.
Anith|||I thought it would be something simple....
Much appreciated.
Tim Morrison
"Green" <subhash.daga@.gmail.com> wrote in message
news:1137098333.822885.223540@.f14g2000cwb.googlegroups.com...
> DISTINCT should be inside the COUNT. Try if this works.
> UPDATE Students SET NumTeachers = (SELECT COUNT(DISTINCT TeacherID)
> FROM Classes WHERE Students.StudentSysID = Classes.StudentSysID)
>