mysql - Inserting data into multiple columns of same table from one column of another table -

i have table "wcr (l,j,w,c,r)" following entries. here, l , j primary keys.

i have insert data column c of wcr table c(l,c1,c2) l primary key. c table follows -

for each l, j=1 inserted in c1, , j=2 inserted c2. cant generalize queries.

i have tried statements -

insert c select 1, c wcr j=1, c wcr j=2;

and subqueries in insert statement -

insert c values (1, select c wcr j=1, select c wcr j=2);

but none of them works in vertica doesn't support subquery in insert statement , first 1 invalid. how can efficiently insert values c ?

one method uses join:

insert c(l, c1, c2)     select wcr1.l, wcr1.c, wcr2.c     wcr wcr1 join          wcr wcr2          on wcr1.l = wcr2.l , wcr1.j = 1 , wcr2.j = 2;

another method uses conditional aggregation:

insert c(l, c1, c2)      select l, max(case when j = 1 c end) c1, max(case when j = 2 c end)      wcr      group l;

Enable