Reputation: 121
I need help phrasing this question. I am new to MySQL. I bet this question was asked, but I do not know the terminology to ask the right question.
Let's say I have two tables in MySQL. Table 1 is where new data gets inserted. Table 2 should automatically update. The data within Table 2 is a sum of specified columns within each row of table 1.
For example,
TABLE 1
| ID | A | B | C | D |
|----+----+----+----+----|
| 0 | 11 | 10 | 39 | 11 |
| 1 | 32 | 21 | 21 | 12 |
| 2 | 12 | 23 | 24 | 13 |
| 3 | 45 | 35 | 31 | 14 |
TABLE 2
| ID | ABCD | AB | CD |
|----+------+----+----|
| 0 | 71 | 21 | 50 |
| 1 | 86 | 53 | 33 |
| 2 | 72 | 35 | 37 |
| 3 | 125 | 80 | 45 |
Upvotes: 0
Views: 42
Reputation: 147146
An alternative to a VIEW
is to add VIRTUAL
generated columns to Table1
. You can add them using an ALTER TABLE
command:
ALTER TABLE Table1
ADD COLUMN ABCD INT GENERATED ALWAYS AS (A+B+C+D) VIRTUAL,
ADD COLUMN AB INT GENERATED ALWAYS AS (A+B) VIRTUAL,
ADD COLUMN CD INT GENERATED ALWAYS AS (C+D) VIRTUAL;
The use of the VIRTUAL
keyword means these values are computed when rows are read, rather than being stored in the table. Having done this, you can now
SELECT * FROM Table1
Output
ID A B C D ABCD AB CD
0 11 10 39 11 71 21 50
1 32 21 21 12 86 53 33
2 12 23 24 13 72 35 37
3 45 35 31 14 125 80 45
Upvotes: 1
Reputation: 520968
I suggest creating a view here:
CREATE VIEW yourView AS (
SELECT
ID,
A + B + C + D AS ABCD,
A + B AS AB,
C + D AS CD
FROM TABLE_1
)
It is probably not an efficient solution to try to actually maintain these data in an actual materialized table, because the data is derived.
Upvotes: 3