CODEWITHSUNDEEP
mysqlsql
Anar Bayramov
Anar Bayramov

Reputation: 11584

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 ?

Upvotes: 0

Views: 77

Answers (2)

Hazonko
Hazonko

Reputation: 1035

something like this perhaps?

UPDATE TOP (19) users set class = "Class 2" where payStatus = 1 and is_completed = 1 and exam_place_id =11 and class = 0

Upvotes: 1

drosam
drosam

Reputation: 2896

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

Upvotes: 1

Related Questions