database - MYSQL How to automatically update a table from another? -

if have 2 tables. table 1 , table 2. table 1 "input table" allows user input values. table 2 "output table" generate answers based on input in table 1.

table 1:

user id | number 1       |   1 2       |   2 3       |   3 

lets table 2 takes values in table 1 , multiply 2. table 2 should be

user id | number 1       |   2        2       |   4     3       |   6 

now, if update table 1, , becomes

table 1:

user id | number     1       |    1     2       |    2     3       |    3     4       |    4     5       |    5     6       |    6 

how can automatic update in table 2 in mysql?

my desired table 2 outcome:

table 2:

user id | number     1       |    2     2       |    4     3       |    6     4       |    8     5       |    10     6       |    12 

lets if table 2 existed, there way use trigger drop current table 2 , create new table 2 when table 1 updated?

of course, use trigger, triggers in case unnecessary. difficult manage , change, unless need process input data in way impossible achieve sql (say need execute third party software), better off creating view.

a view special type of table defined sql query on existing data. in provided example, create such table running:

create view table2     select userid, number * 2 number table1; 

here, instructing database create view or otherwise virtual table named table2, contents of defined result of select query.

to access output data run:

select * table2; 

what better, no matter how change data of table1 (by inserting, updating or deleting), view reflect these changes. if decide alter structure of table1, there no issue, long still contains userid , number attributes. if decide need more (or less) data included in table2, drop view , create new 1 without losing data.

see how create view in mysql?, what views for? well.