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

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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 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,

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
FROM  students
LEFT JOIN  `studentclasslink` ON students.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,


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

Quoted text here. Click to load it

This version of MySQL means you cannot use subqueries, or multi-table UPDATE

Quoted text here. Click to load it

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 =
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... )

Bill K.

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

Quoted text here. Click to load it

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


Site Timeline