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 "studentclassli nk"
which has fields, student_id, class_id as well as it's own primary key,
"studentclassli nk_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.studen t_id, students.studen t_name, count(class_id) as
classcount
FROM students
LEFT JOIN `studentclassli nk` ON students.studen t_id =
studentclasslin k.student_id,
GROUP BY students.studen t_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

[Colin McKinnon]

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

Dave Smithz wrote:
[color=blue]
> 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.
>[/color]

OK so you want to subvert the normalization your database. This is
marginally more acceptable than cross-posting.
[color=blue]
>
> Now how would I go about writing a query that updates the "status" field
> only for students that have attended no classes?[/color]

I don't know ow *you* would go about it. My first thought would be a
subselect in an update.
[color=blue]
> Bear in mind that my host is running MySQL version 3.23.[/color]

So that rules out subselects.
[color=blue]
> SELECT students.studen t_id, students.studen t_name, count(class_id) as
> classcount
> FROM students
> LEFT JOIN `studentclassli nk` ON students.studen t_id =
> studentclasslin k.student_id,
> GROUP BY students.studen t_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).[/color]

Messy if you only want to find out whom has not attended:

SELECT a.student_id
FROM students a LEFT JOIN studentclasslin k 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.

[Dave Smithz]

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


Thanks for the input.[color=blue]
> OK so you want to subvert the normalization your database. This is
> marginally more acceptable than cross-posting.[/color]
Did not understand the first sentence. Is this a good or bad thing?[color=blue]
> So that rules out subselects.[/color]
Yes it is annoying that I have to support MySQL 3
[color=blue]
>
>
> Messy if you only want to find out whom has not attended:
>
> SELECT a.student_id
> FROM students a LEFT JOIN studentclasslin k b
> ON a.student_id=b. student_id
> HAVING b.student_id IS NULL[/color]

Good point in finding how many members have not attended. The SQL I gave was
to more demonstrate my problem. Even with the above code, I still do not
know how I apply this to an update situation.
My worse case scenario is to in the PHP code store the row id's of the rows
that need to be updated. But I was thinking there should be an SQL method.
[color=blue]
> As to what you do next - I guess you already know that since you posted to
> comp.lang.php (amongst others)[/color]

Not wanting to get away from the main pont, I only posted into two related
groups. Sometimes it is not clear to which group to post. I do not see why
it is such a problem cross posting if the groups are carefully selected and
relevant. Maybe it is the Newsreader I use does not make it a problem, and
maybe I will now hear otherwise, but what really is the big problem with the
occasional cross posting on groups that are related. Surely it is better
then posting the same message twice into each group but at different times.

Kind regards

Dave

[Jerry Stuckle]

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

Dave Smithz wrote:[color=blue]
> Thanks for the input.
>[color=green]
>>OK so you want to subvert the normalization your database. This is
>>marginally more acceptable than cross-posting.[/color]
>
> Did not understand the first sentence. Is this a good or bad thing?
>[color=green]
>>So that rules out subselects.[/color]
>
> Yes it is annoying that I have to support MySQL 3
>
>[color=green]
>>
>>Messy if you only want to find out whom has not attended:
>>
>>SELECT a.student_id
>>FROM students a LEFT JOIN studentclasslin k b
>> ON a.student_id=b. student_id
>>HAVING b.student_id IS NULL[/color]
>
>
> Good point in finding how many members have not attended. The SQL I gave was
> to more demonstrate my problem. Even with the above code, I still do not
> know how I apply this to an update situation.
> My worse case scenario is to in the PHP code store the row id's of the rows
> that need to be updated. But I was thinking there should be an SQL method.
>
>[color=green]
>>As to what you do next - I guess you already know that since you posted to
>>comp.lang.p hp (amongst others)[/color]
>
>
> Not wanting to get away from the main pont, I only posted into two related
> groups. Sometimes it is not clear to which group to post. I do not see why
> it is such a problem cross posting if the groups are carefully selected and
> relevant. Maybe it is the Newsreader I use does not make it a problem, and
> maybe I will now hear otherwise, but what really is the big problem with the
> occasional cross posting on groups that are related. Surely it is better
> then posting the same message twice into each group but at different times.
>
> Kind regards
>
> Dave
>
>[/color]

Dave,

I agree this is subverting the normalization process. Why update a
field with this information? Just use your select statement, adding

HAVING count(class_id) = 0;

This will get all the students with no classes.

Otherwise you need to run the update statement before you run the select
- duplicate work.

As to whether to post here or someplace else. This is the wrong place
to post. You have a SQL question, not a PHP question.
comp.databases. mysql would be a much better place to ask this question.



--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Bill Karwin]

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

"Dave Smithz" <dave1900@bluey onder.co.uk> wrote in message
news:B7SAf.1818 54$vl2.169835@f e2.news.blueyon der.co.uk...[color=blue]
> 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.[/color]

This version of MySQL means you cannot use subqueries, or multi-table UPDATE
statements.
[color=blue]
> SELECT students.studen t_id, students.studen t_name, count(class_id) as
> classcount
> FROM students
> LEFT JOIN `studentclassli nk` ON students.studen t_id =
> studentclasslin k.student_id,
> GROUP BY students.studen t_id[/color]

That is very close. I'd do this:

SELECT S.student_id
FROM students AS S LEFT JOIN studentclasslin k 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]

Re: Update query needed - on a &quot;group by&quot; query - Possible / How???


"Bill Karwin" <bill@karwin.co m> wrote in message
news:dr3m4s02n9 a@enews4.newsgu y.com...
[color=blue]
> 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... )[/color]

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