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 Morrison

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)

I thought it would be something simple.....

Much appreciated.

Tim Morrison

Show quoteHide quote

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.

What is this called, and how to do it?
SQL Server vs the Competition
Date Only Data Type
Select Records which are not present in second table
Retreiving multiple Output Parameters