Mysql tables does not reflect new changes

Question

I have created 4 tables by using command line in 'college2' database:

mysql> SHOW TABLES;
+--------------------+
| Tables_in_college2 |
+--------------------+
| course             |
| enrolment          |
| enrolment_status   |
| student            |
+--------------------+
4 rows in set (0.00 sec)

The table enrolment_status counts the number of enrolled students in each course, from the table enrolment, and is created using the following command line:

CREATE TABLE enrolment_status SELECT code, count(id) FROM enrolment
GROUP BY code;

When i insert a new record to enrolment table, i expect to see a corresponding change in enrolment_status table, but enrolment_status table remains unchanged, does not reflect newly inserted record.

How should i correct this?

Answer 1

Using CREATE TABLE with another table as the data source provides the target table with a one time snapshot of the data in the source table. Hence, making subsequent inserts into enrolment will not reflect in enrolment_status. The closest thing to what you have in mind might be to create a view on top of the enrolment table:

CREATE VIEW enrolment_view AS
SELECT code, COUNT(id)
FROM enrolment
GROUP BY code;

The view enrolment_view would reflect changes to the underlying table enrolment, including newly inserted records. But, it would not be possible to insert records directly into this view.