mysql - 如何为mysql数据库中的限制人数赋予不同的值？

Question

我有学生和类(class)。每个学生都有一个 exam_place_id 和教室每个教室都有自己的座位限制。所以我必须根据每个学生可以容纳的学生数量来分配教室。

例如，在 Istanbul 尔 (exam_place_id= 11)，我现在有 530 名学生。

类(class)配额就像

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

因此，根据类(class)配额，我将更新学生的类(class)

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

因此，我将从表中获取 19 个用户，并检查其中哪些用户的类别值为零(未定义)，我将使用我的类别编号更新它们。但没有成功，它会覆盖之前的一个，即使我尝试更新学生类别 = 0

我还可以尝试哪些其他解决方案？

Answer 1

据我们所知，class列类型为varchar，因此0不是一个可能的值。您想检查列值是否为 NULL 或空吗？

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