Update problem

[Jon Cosby]

In this update, "courses" has the course number for each course and the
number of corresponding credits; "courseenrollme nt" has the course number
and id for students enrolled in that course; "students" has the student id
and total credits for each student.

UPDATE students SET credits = (SELECT Sum(c.credits)
FROM courses c INNER JOIN courseenrollmen t ce
ON c.coursenum = ce.coursenum
INNER JOIN students s ON s.sid = ce.sid)
FROM courses, courseenrollmen t, students

The SELECT query returns the total for ALL students and sets the value for
each student accordingly. I've tried using distinct clauses,

....
WHERE ce.sid IN (SELECT DISTINCT ce1.sid FROM courseenrollmen t ce1))
....

What's another way to do this?


Jon Cosby

[John Gilson]

Re: Update problem

"Jon Cosby" <jcosby@mindspr ing.com> wrote in message news:bh0bvm$sge $1@slb2.atl.min dspring.net...[color=blue]
> In this update, "courses" has the course number for each course and the
> number of corresponding credits; "courseenrollme nt" has the course number
> and id for students enrolled in that course; "students" has the student id
> and total credits for each student.
>
> UPDATE students SET credits = (SELECT Sum(c.credits)
> FROM courses c INNER JOIN courseenrollmen t ce
> ON c.coursenum = ce.coursenum
> INNER JOIN students s ON s.sid = ce.sid)
> FROM courses, courseenrollmen t, students
>
> The SELECT query returns the total for ALL students and sets the value for
> each student accordingly. I've tried using distinct clauses,
>
> ...
> WHERE ce.sid IN (SELECT DISTINCT ce1.sid FROM courseenrollmen t ce1))
> ...
>
> What's another way to do this?
>
>
> Jon Cosby[/color]

UPDATE Students
SET credits = (SELECT SUM(C.credits)
FROM CourseEnrollmen t AS CE
INNER JOIN
Courses AS C
ON CE.sid = Students.sid AND
C.coursenum = CE.coursenum)

Regards,
jag