这是一个简单的步骤，也许你的问题会得到解决。

create table tab_b(id int,
                   A_id int,
                   `key` varchar(20),
                   `value` varchar(20)); 
insert into tab_b values
(1,1,'text1','test_value1'),
(2,1,'text2','test_value2');

create table tab_a(id int,text1 varchar(20),text2 varchar(20));

insert into tab_a(id,text1,text2)
select id,max(text1) text1,max(text2) text2
from(
select a_id as id,
       case when `key`='text1' then `value` end text1,
       case when `key`='text2' then `value` end text2
from tab_b) a;

演示

加入 B 两次，每个“键”一次。

UPDATE `A`
       INNER JOIN `B` `B1`
                  ON `B1`.`A_id` = `A`.`id`
                     AND `B1`.`key` = 'text1'
       INNER JOIN `B` `B2`
                  ON `B2`.`A_id` = `A`.`id`
                     AND `B2`.`key` = 'text2'
       SET `A`.`text1` = `B1`.`value`,
           `A`.`text2` = `B2`.`value`;