|
|
Posted by Colin McKinnon on 01/22/06 23:55
Dave Smithz wrote:
> I need to update a table based on a group by
> query.
>
> For example, I have a table called "students". I need to update a field
> called "status" on this table for all members that have never attended a
> class.
>
OK so you want to subvert the normalization your database. This is
marginally more acceptable than cross-posting.
>
> Now how would I go about writing a query that updates the "status" field
> only for students that have attended no classes?
I don't know ow *you* would go about it. My first thought would be a
subselect in an update.
> Bear in mind that my host is running MySQL version 3.23.
So that rules out subselects.
> SELECT students.student_id, students.student_name, count(class_id) as
> classcount
> FROM students
> LEFT JOIN `studentclasslink` ON students.student_id =
> studentclasslink.student_id,
> GROUP BY students.student_id
>
> This will give me a list of each student and how many classes they have
> attended. I want to update all the students who have not attended any
> classes (therefore a classcount of 0).
Messy if you only want to find out whom has not attended:
SELECT a.student_id
FROM students a LEFT JOIN studentclasslink b
ON a.student_id=b.student_id
HAVING b.student_id IS NULL
As to what you do next - I guess you already know that since you posted to
comp.lang.php (amongst others)
C.
Navigation:
[Reply to this message]
|