0

I have table A, 3 columns 

id text1 text2

I have table B, 4 columns

id A_id key value

Data in table B looks like

id  A_id   key     value 
1    1     text1   test_value1 
2    1     text2   test_value2

Each row in table A maps to 2 rows in table B. And columns text1 and text2 on table A are empty right now. I am trying to copy data from table B to table A.

How can I do that in 1 stament? Thanks. Right now I have below statement but have to specify 

AND B.key = "text1"

, how can I update columns text1 and text2 in the same time? 

UPDATE A
    INNER JOIN B
        ON A.id = B.A_id
        AND B.key = "text1"
SET A.text1 = B.value
user8355591
  • 181
  • 1
  • 10
  • Possible duplicate of https://stackoverflow.com/questions/11709043/mysql-update-column-with-value-from-another-table and https://stackoverflow.com/questions/12394506/mysql-update-table-based-on-another-tables-value? – anmari Jul 15 '18 at 06:37

2 Answers2

2

Join B twice, once for each "key".

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`;
sticky bit
  • 36,626
  • 12
  • 31
  • 42
0

Here is the simple step, that might your problem will be solved.

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;

DEMO

Vivek
  • 783
  • 5
  • 11