How to automatically update table, whenever data added to another table?
I have created two tables in MySQL. One (
Todo_tbl) holds the data while the other (
Statistics_tbl) hold the sums one of the variables from
Following the definitions of these tables:
create table Todo_tbl ( id INT auto_increment, person VARCHAR(45) , task VARCHAR(45) , duration INT(4), deadline_day VARCHAR(2), deadline_month VARCHAR(2), PRIMARY KEY(id) )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
insert into Todo_tbl values(1,'John', 'dust the floors', 40,04,03);
insert into Todo_tbl values(2,'Matt', 'do the dishes', 15,02,02);
insert into Todo_tbl values(3,'Mary', 'dusting', 40,03,02);
insert into Todo_tbl values(4,'Chloe', 'cleaning the windows', 75,04,05);
insert into Todo_tbl values(5,'John', 'wash the floors', 60,03,03);
insert into Todo_tbl values(6,'Bridget', 'take out the trash', 15,03,03);
insert into Todo_tbl values(7,'Matt', 'do the laundry', 18,02,02);
insert into Todo_tbl values(8,'Bridget', 'water the plants', 15,03,03);
select * from Todo_tbl;
create table Statistics_tbl (
SELECT person, SUM(duration) as total_duration FROM Todo_tbl GROUP BY person
select * from Statistics_tbl;
The problem is that whenever I add new data to the
Todo_tbl, it is not considered in the
Is there any way to connect two tables, so that when there is a change in one, the other will respond as well?
Marcee last edited by
Why do you need a table to hold the statistics when you have a perfectly good query that can get you the same thing from the original table?
Databases like to stick to a concept known as the "Single Source of Truth".
One table (or a group of them) holds the fundamental data. If that's wrong, everything's wrong. In this case, that's your todo_tbl.
If you need another table that has to be kept in step with todo_tbl - however that's done - then you run the risk of the two getting out of step and misrepresenting what's what.
Suggestion: Pop an index on Todo_tbl.person (may help with the grouping) and use the select statement you already have to get the statistics as the heart of a View that gets you the statistics data straight from the Single Source of Truth.
CREATE VIEW statistics_v AS SELECT person , SUM( duration ) as total_duration FROM todo_tbl GROUP BY person ;