Rodrane - 1 year ago 51

MySQL Question

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

what are the any other solutions I can try ?

Answer Source

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
```