Posted by Bill Karwin on 01/24/06 00:42

"Dave Smithz" <dave1900@blueyonder.co.uk> wrote in message
news:B7SAf.181854$vl2.169835@fe2.news.blueyonder.co.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.

[Back to original message]