MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t1 (id integer, t2id integer, name varchar(8));
create table t2 (id integer, name varchar(8));
insert into t2 values (1, 't2 name1') 
insert into t2 values (2, 't2 name2') 
insert into t1 (id, t2id, name) 
    values (1, 1, (select name from t2 where t2.id = t2id))
Unknown column 't2id' in 'where clause'

insert into t1 (id, t2id, name) 
    values (1, 1, (select name from t2 where t2.id = 1))
insert into t1 (id, t2id, name) 
    select 2, id, name from t2 where t2.id = 2
select * from t1


id
t2id
name


1
1
t2 name1

2
2
t2 name2