Pong,
	The reason for the error message is that the statement  (select
count(courseid) from dss_enroll where grade is
not null group by courseid) returns more than on value, you are trying to
set countgraded to this set of values.

try:

update dss_course 
set countgraded = ct.coursecount
from dss_course C
inner join dss_enroll E on ( C.courseid = E.courseid )
left outer join (select courseid , count(courseid) as coursecount from
dss_enroll where grade is
not null group by courseid) ct on ( C.courseid = ct.courseid )

Nick

-----Original Message-----
From: Pongpunt Pungchai [mailto:pong@t...]
Sent: 18 July 2002 00:26
To: sql language
Subject: [sql_language] Update Statement



Hello All

I try to update a field in one table by counting records in the other
table.  My update statement is as follows:
<b>
update dss_course 
set countgraded = (select count(courseid) from dss_enroll where grade is
not null group by courseid)
from dss_course C, dss_enroll E
where C.courseid = E.courseid
</b>

I got the error message as follows:
<b>
Subquery returned more than 1 value. This is not permitted when the 
subquery follows =, !=, <, <= , >, >= or when the subquery is used as an 
expression.
The statement has been terminated.
</b>

Courseid is PK in dss_course and FK in dss_enroll.  I use MSSQL2000. Can 
you please help?

Thank you.
Pong



*********************************************************************
This message and any attachments are confidential and should only be
read by those to whom they are addressed. If you are not the intended
recipient, please contact us, delete the message from your computer 
and destroy any copies. Any distribution or copying without our prior
permission is prohibited. 

The Achilles Group does not accept legal responsibility for the 
contents of this message. The recipient is responsible for verifying 
its authenticity before acting on the contents. Any views or opinions 
presented are solely those of the author and do not necessarily 
represent those of the Achilles Group.

This email has been scanned by MAILsweeper and Sophos Anti-Virus 
products. However, The Achilles Group will have no liability for any 
viruses contained in this email or any attachment with this email.
*********************************************************************