changing the value of a column in a table based on the value of another column I have the following table that is the &quot;Class&quot; table:




class_size
class_number
student_id





2
4005



1
4007



2
4001



3
4010




I want the &quot;class_size&quot; 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.
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