Rodrane Rodrane - 3 months ago 13
MySQL Question

How to give different values to number of limit people in mysql database?

I have students and classes.
Each student has an exam_place_id and classroom
every classroom has its own sitting limit.
so I have to give everystudent classroom depending on number of students it can handle.

For example in Istanbul (exam_place_id= 11) I have 530 students right now.

and class quotas are like

Class 1 - number of students can sit : 19
Class 2 - number of students can sit : 26
Class 3 - number of students can sit : 29
Class 4 - number of students can sit : 24
Class 5 - number of students can sit : 31
Class 6 - number of students can sit : 22
Class 7 - number of students can sit : 24
Class 8 - number of students can sit : 29
Class 9 - number of students can sit : 24
Class 10 - number of students can sit : 25
Class 11 - number of students can sit : 28
Class 12 - number of students can sit : 24
Class 13 - number of students can sit : 22
Class 14 - number of students can sit : 28
Class 15 - number of students can sit : 28
Class 16 - number of students can sit : 27
Class 17 - number of students can sit : 64
Class 18 - number of students can sit : 64


so depending on class quotas I was going to update student's classes

update students set class = "Class 1" where payStatus = 1 and is_completed = 1 and exam_place_id =11 and class = 0 limit 19

update users set class = "Class 2" where payStatus = 1 and is_completed = 1 and exam_place_id =11 and class = 0 limit 26


So I'll take 19 users from table and check which of them's class value is zero (not defined) I'll update them with my class number. but didnt work it overwrites the one before even though I try to update students with
class = 0

what are the any other solutions I can try ?

Answer

As far as we know class column type is varchar, so 0 is not a possible value. Did you want to check if the column value is NULL or empty?

UPDATE students SET class = "Class 1" 
WHERE payStatus = 1 AND is_completed = 1 AND 
      exam_place_id =11 AND (class IS NULL OR class = '') 
LIMIT 19