1

I've created in MySQL form1 with table1 and table2. Table1 keeps data like firstname, lastname, age and Table2 keepds data of bodyweight and bodyfat.

I used this code in order to make a new table3 where firstname, lastname, and bodyweight are displayed (but only the data that are already inserted to the tables):

Create table table3 as
Select table1.firstname, table1.lastname, table2.bodyweight
From table1
LEFT JOIN table2 ON table1.table1_id = table2.table2_id;

The problem is that when I'm inserting new data to table1 they aren't been displayed to table3... Any thoughts on what may be the cause?

Dharman
  • 30,962
  • 25
  • 85
  • 135
thanos_zach
  • 156
  • 4
  • 20

1 Answers1

1

Creating a table in such a way copies the data that was selected and inserts it to the new table. Once this is done, there's no longer any relation between this new table and the tables used in query.

If you want to automatically update whenever table1 and table2 are updated, you can create a view instead:

Create view table3 as
Select table1.firstname, table1.lastname, table2.bodyweight
From table1
LEFT JOIN table2 ON table1.table1_id = table2.table2_id;
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Hello again! quick question. Is there any way to make views editable like tables? – thanos_zach Oct 10 '17 at 08:07
  • @ath_zach yes, but not with a `left join`. See MySQL's documentation on [Updatable and Insertable Views](https://dev.mysql.com/doc/refman/5.7/en/view-updatability.html) for more details. – Mureinik Oct 10 '17 at 08:39
  • Thanks again. Unfortunately it seems a bit confusing for me so i made a new question based on this example so i would be thankful if you could help there too https://stackoverflow.com/questions/46670507/make-sql-view-editable – thanos_zach Oct 10 '17 at 15:31