how can I insert new rows from table 2 in table 1 with the corresponding date?

I have two tables that I want to join using a date field:

• Table 1: there’re multiple records for each date
• Table 2: there’s only 1 record for each date

How can I update records in Table 1 with fields from Table 2 corresponding the date. Here’s an example:

table1 
column1    column2   column3
03/02/2018 .......   ......
03/02/2018 .......   ......
03/02/2018 .......   ......
04/02/2018 .......   ......
04/02/2018 .......   ......
05/02/2018 .......   ......
05/02/2018 .......   ......

table2
column4     column5   column6
03/02/2018  .......   .......
04/02/2018  .......   .......
05/02/2018  .......   .......

here is what I would like to do

table1
column1    column2   column3  column4   column5   column6
03/02/2018 .......   ......
03/02/2018 .......   ......
03/02/2018 .......   ......   .......    ......   .......  <= same date 
04/02/2018 .......   ......
04/02/2018 .......   ......   .......    ......   .......  <= same date
05/02/2018 .......   ......
05/02/2018 .......   ......   .......    ......   .......  <= same date

thanks!

Answer

You seem to just want a left join:

select t1.*, t2.column5, t2.column6
from table1 t1 left join
     table2 t2
     on t1.column1 = t2.column4;

I don’t see value in actually adding these columns to the table. With both tables in the same database and indexes on the join columns, the performance for running the query should be fine.

If you really want to update table1, you need to start by altering the table to have the new columns (which can be an expensive operation) and then updating the values in the columns.