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 5 1997
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.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).
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.
Thanks for the input. OK so you want to subvert the normalization your database. This is marginally more acceptable than cross-posting.
Did not understand the first sentence. Is this a good or bad thing? So that rules out subselects.
Yes it is annoying that I have to support MySQL 3
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
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.
As to what you do next - I guess you already know that since you posted to comp.lang.php (amongst others)
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
Dave Smithz wrote: Thanks for the input.
OK so you want to subvert the normalization your database. This is marginally more acceptable than cross-posting.
Did not understand the first sentence. Is this a good or bad thing?
So that rules out subselects.
Yes it is annoying that I have to support MySQL 3
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
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.
As to what you do next - I guess you already know that since you posted to comp.lang.p hp (amongst others)
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
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. js*******@attgl obal.net
=============== ===
"Dave Smithz" <da******@bluey onder.co.uk> wrote in message
news:B7******** *************@f e2.news.blueyon der.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.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
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.
"Bill Karwin" <bi**@karwin.co m> wrote in message
news:dr******** *@enews4.newsgu y.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Louis |
last post by:
Is there a switch or a setting in Access so that a group by query
doesn't return a field name SumOf(original field name)? Especially
when you chain multiple queries together you'd get SumOfSumOfSumOf....
Anyone know? TIA.
|
by: Dave Smithz |
last post by:
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
|
by: richforsandy |
last post by:
I am working with a two field table of 144,020 rows.
The PERSONID field is the FK and the ADDRESSID is the PK.
FK is a number type, PK is autonumber.
PERSONID represents employees, ADDRESSID represents addresses.
The objective is to mark all single addresses as primary and all double addresses (for the same employee) as 'primary and secondary'.
|
by: laurenquantrell |
last post by:
I have a table with 6 million rows.
I'm trying to perform a group by query:
SELECT COUNT(CityID) AS Cnt, Country
FROM dbo.tblCitiesWorld
WHERE (FC = N'p')
|
by: laurenquantrell |
last post by:
I have a table with 6 million rows.
I'm trying to perform a group by query that incudes a row count of the
PK column:
SELECT COUNT(DataID) AS Cnt, Column1Name
FROM dbo.TableName
WHERE (Column2Name = N'p')
GROUP BY Column1Name
I have the query timeout set to 0 in Enterprise Manger. However, if I
| |
by: Sebastien.LICHTHERTE |
last post by:
Hello,
I need to calculate the median and percentile of values in a group by
query satisfying several criteria the user is asked to fill in when
opening the query.
A have a table called RX with this kind of data :
HOSP TYPE SUB DAP
A THO F 1
|
by: =?Utf-8?B?VmFuZXNzYQ==?= |
last post by:
Here is my loop and it runs fine:
----------------------------------------------------
sSQL = "SELECT * FROM STORE_ITEMS"
Set DataRec = DB.execute(sSQL)
if not DataRec.EOF then
do while not DataRec.EOF
SKU = trim (DataRec("SKU"))
ITEM_ID = trim(DataRec("ITEM_ID"))
...
|
by: dilippanda |
last post by:
Hi,
Please help me in the following query.
SELECT
a.bcast_id ,
a.bcast_file_nm ,
a.clickthru_url_cd ,
a.list_id ,
a.prs_id ,
|
by: deepakkumars |
last post by:
Hi All:
I'm using Access 2003 on Windows XP.
I'm creating a table where i'll be adding a new variable every week (based on the week number). This is a numeric variable. Once the variable is inserted, i need to run a GROUP BY query based on other character variables. For instance, i'm having a table as:
EMP_ID
Sales_week1
Sales_week2
Sales_week3,
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |