sql - 从具有不同键 Postgres 的同一列复制列中的值

Question

我在 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  

我如何将第 3 列的值(其中 col2= Sample)复制到第 3 列(其中 col2 = 对应的 col1 和 id 值的样本类型)

Answer 1

您可以像这样更新表:

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;