I have the following table that is the "Class" table:

class_size class_number student_id
2 4005
1 4007
2 4001
3 4010

I want the "class_size" column to be generated automatically based on the other two columns. I have tried the following SQL statement, but it doesn't work:

update Class 
set class_size=totalSize 
where totalSize,Cnum in (Select class_number as Cnum ,count(student_id) as totalSize 
from Class
group by class_number)
and class_number = Cnum;

I really appreciate it if someone can help me with this problem, as I tried many different statements and still I cannot get it right.

  • Would you state how to calculate class size?
    – McNets
    Apr 4 at 19:14
  • I calculate the class size based on the number of people taking it. for example in the table I showed, two people with student IDs 4005 and 4001 are in the class_number 2, so the class size of the class_number 2 is 2. On the other hand, only one person with the student ID 4010 is in the class_number 3, so the class_size of the class_number 3 is 1.
    – annita
    Apr 4 at 20:39

here is the syntax :

 update class
 inner join (
    select class_number, count(*) totalSize
    from class
    group by class_number
 ) cc on class.class_number = cc.class_number
 set class.class_size = totalSize

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.