Update query needed - on a "group by" query - Possible / How???

[Dave Smithz]

Hi there,

Been working on an evolving DB program for a while now. Suddenly I have come
across a situation where 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.

Class attendance is recorded by another table (which represents the many to
many relationship between a student and a class) called "studentclasslink"
which has fields, student_id, class_id as well as it's own primary key,
"studentclasslink_id".

Now how would I go about writing a query that updates the "status" field
only for students that have attended no classes?
At first I thought this was easy as I can display a count of how many
classes a student has attended by a query like so:

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).
Bear in mind that my host is running MySQL version 3.23.

Kind regards,

Dave

[Bill Karwin]

"Dave Smithz" <da******@blueyonder.co.uk> wrote in message
news:B7*********************@fe2.news.blueyonder.c o.uk...

Now how would I go about writing a query that updates the "status" field
only for students that have attended no classes?
Bear in mind that my host is running MySQL version 3.23.
This version of MySQL means you cannot use subqueries, or multi-table UPDATE
statements.
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

That is very close. I'd do this:

SELECT S.student_id
FROM students AS S LEFT JOIN studentclasslink AS L ON S.student_id =
L.student_id
WHERE L.student_id IS NULL

Fetch the list of student_id values, and format the list as a string with
values comma-separated.
Then create an UPDATE statement including that string:

UPDATE students
SET status = ...value...
WHERE student_id IN ( ...comma-separated list of values from previous
query... )

Regards,
Bill K.

[Dave Smithz]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:dr*********@enews4.newsguy.com...

Fetch the list of student_id values, and format the list as a string with
values comma-separated.
Then create an UPDATE statement including that string:

UPDATE students
SET status = ...value...
WHERE student_id IN ( ...comma-separated list of values from previous
query... )

OK, thanks for this. This is the method I went for in the end. Therefore
writing a PHP script to run a query to get all the ID's I needed to update
and then running a second query. I think I was trying to hard to do this in
a single SQL statement without subqueries.
I just assumed it would be possible to do conditional updates based on
values when grouping, but I guess it is more complicated then that.

Thanks for all the input.

Kind regards

Dave