Copy values in column from same column with different key Postgres

Question

I have following table in postgres

id | col1         |col2      |col3  |col4
66 | AfBLy_2_d1_1 | Sample |6798  | Day 1  
67 | AfBLy_2_d1_6 | Sample |8798 | Day 2  
66 | AfBLy_2_d1_4 | Sample |6776 | Day 7  
69 | AfBLy_2_d1_9 | Sample |6789 | Day 5  

66 | AfBLy_2_d1_1 | Sample Type  |  | Day 1
69 | AfBLy_2_d1_6 | Sample Type |  | Day 2  
66 | AfBLy_2_d1_4 | Sample Type |  | Day 7  
67 | AfBLy_2_d1_9 | Sample Type |   | Day 5

How do i copy value of column 3 where col2= Sample to column 3 where col2 = Sample Type for corresponding col1 and id values

Gurwinder Singh · Accepted Answer · 2017-01-17 05:25:04Z

1

You can UPDATE the table like this:

with cte as (
    select * from your_table
    where col2 = 'Sample'
)
update your_table as t1
set t1.col3 = t2.col3
from cte as t2
where t1.col2 = 'Sample Type'
and t1.col1 = t2.col1
and t1.id = t2.id;

answered Jan 17, 2017 at 5:25

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vivek S. · Accepted Answer · 2017-01-17 05:47:17Z

1

update tbl t
set t.col3 = t1.col3
from (
    select id,col1,col3
    from tbl
    where col2 = 'Sample'
    ) t1
where t.col2 = 'Sample Type'
    and t.col1 = t1.col1
    and t.id = t1.id

edited Jan 17, 2017 at 5:47

answered Jan 17, 2017 at 5:39

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

Copy values in column from same column with different key Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related