update values of table based on foreign key-CodePudding

I've two tables like below (database MYSQL):

Table1
id 
col1

Table2
id 
col1 -> foreign key(Table1 - id)
col2

Now I want to insert value into Table2(col2) for all rows with the following condition:

Get value from Table1(col1) where Table2(col1) = Table1(id)

Example:

Before Insert:

Table1 

id col1
1  value1  
2  value2  

Table2

id col1(fk)    col2
3  1           NULL
4  2           NULL

After Insert:

Table2

id col1(fk)    col2
3  1           value1
4  2           value2

I tried insert into with select join and where but apparently couldn't get it to work

insert into Table2(col2)
select t1.col1 from Table1 t1 join Table2 t2 on t1.id = t2.col1

Any pointers ?

Update

Got it working. Thanks for the pointers @rahul @frank I actually need to do update

update Table2 t2
set col2 = (SELECT t1.col1 FROM Table1 t1 where t1.id = t2.col1);

CodePudding user response：

Update with JOIN

-- MySQL
UPDATE Table2
INNER JOIN Table1 
        ON Table2.col1 = Table1.id
SET Table2.col2 = Table1.col1;

Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a28fec2da45aa634f2509ec9299c2bed