MySQL update based on several parameters

Question

In my database a Teacher has a TeacherPIN, Name and Pay. My database also has a Class which contains a ClassPIN as well as a TeacherPIN and Student that has a reference to TeacherPIN as well as several ClassPINs. I want to update a certain teacher's pay based on if they have more than 25 students in a certain class, but am stuck. Currently I'm doing:

update Teacher
set Pay = Pay + 1000
where (TeacherPIN = c.TeacherPIN from Class c 
and c.ClassPIN = '1010')
and (select count(s.ClassPIN) from Student s 
where s.ClassPIN = '1010') >= 25;

I know this is probably very wrong and would love a nudge in the right direction.

Take a look at http://stackoverflow.com/questions/806882/update-multiple-tables-in-mysql-using-left-join and keep in mind that you can use any type of join here, not just left joins and that should get you a bit closer to what you want. — Matt Glover, Feb 07 '12 at 04:48
Can you detail the Student table, please? Provide all the fields it contains and please explain why it has a reference to Teacher table — Mosty Mostacho, Feb 07 '12 at 05:52

score 0 · Accepted Answer · answered Feb 07 '12 at 06:01

You could try the following:

UPDATE Teacher t
SET Pay = Pay + 1000
LEFT JOIN Class c ON c.TeacherPIN=t.TeacherPIN
LEFT JOIN Student s ON s.ClassPIN = c.ClassPIN
WHERE COUNT(IF(s.ClassPin='1010',1,NULL))>25;

The two JOINs bring in the class and student information, and the COUNT(IF(s.ClassPin='1010',1,NULL)) counts up how many s.ClassPins there are under '1010'.

(I'd check it first though:

SELECT * 
FROM Teacher t
LEFT JOIN Class c ON c.TeacherPIN=t.TeacherPIN
LEFT JOIN Student s ON s.ClassPIN = c.ClassPIN
WHERE COUNT(IF(s.ClassPin='1010',1,NULL))>25;

)

This does assume there is only one teacher per class.

MySQL update based on several parameters

1 Answers1