MYSQL How to automatically update a table from another?

Question

If I have two tables. table 1 and table 2. table 1 is the "input table" that allows user to input values. table 2 is the "output table" that generate answers based on the input in table 1.

table 1:

user ID | Number
1       |   1
2       |   2
3       |   3

lets say table 2 takes values in table 1 and multiply by 2. so table 2 should be

user ID | Number
1       |   2       
2       |   4    
3       |   6

now, if I update table 1, and it becomes

table 1:

user ID | Number    
1       |    1    
2       |    2    
3       |    3    
4       |    4    
5       |    5    
6       |    6

How can I get an 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 say if Table 2 is already existed, Is there a way to use trigger to drop the current table 2 and create a new table 2 when the table 1 is updated?

If they're keyed look at `ON UPDATE CASCADE` if they're not look at using a `TRIGGER` — Tom, Aug 18 '17 at 17:03

score 2 · Accepted Answer · answered Aug 20 '17 at 10:36

Of course, you could use a trigger, but triggers in this case are completely unnecessary. They are difficult to manage and change, so unless you need to process the input data in a way that is impossible to achieve with SQL (say you need to execute some third party software), you are really better off creating a view.

A view is a special type of table that is defined as an SQL query on existing data. In the provided example, you would create such a table by running:

CREATE VIEW table2 AS
    SELECT userId, number * 2 as number FROM table1;

Here, we are instructing the database to create a view or otherwise virtual table named table2, the contents of which are defined as the result of the select query.

To access your output data you could then simply run:

SELECT * FROM table2;

What is even better, is that no matter how you change the data of table1 (by inserting, updating or deleting), the view will reflect these changes. If you decide to alter the structure of table1, there is no issue, as long as it still contains the userID and the number attributes. If you were to decide that you need more (or less) data included in table2, you could simply drop this view and create a new one without losing data.

See How do I create a view in MySQL?, What are views good for? as well.

score 0 · Answer 2 · answered Aug 18 '17 at 17:40

0

You can create a Trigger that will updates the column(Number) of Table 2 when a column in Table 1 is updated.

Below is the prototype .

CREATE OR REPLACE TRIGGER your_trigger_name
    BEFORE UPDATE ON Order
    FOR EACH ROW 
BEGIN
   //Fetch each row of Col Number of Table 2 in loop using a cursor and update/set its value.

select number into var_x from table 2 .
       UPDATE Table 2 
       set number = :2*var_x ;

    end if;
END;

answered Aug 18 '17 at 17:40

nitin.sharma0180

471
3
6

Since you reuse your table instead of dropping and re-creating it just use TRUNCATE. – nitin.sharma0180 Aug 18 '17 at 18:24

score 0 · Answer 3 · answered Aug 18 '17 at 18:55

0

U need to use after update trigger

Create trigger triggername After insert or update on table1 For each row Begin Insert into table2(new.userid,new.Number*2); End

answered Aug 18 '17 at 18:55

CHAITU

1
2

MYSQL How to automatically update a table from another?

3 Answers3