postgreSQL查询然后修改

Tip:

不能直接关联修改，需要一个临时表。

-- 修改 origin_ems_transactions 表 pmd_program_id 字段
with tem as(
select oet.product,lbnbv.pmd_program_id  
from origin_ems_transactions oet 
join ludp_bq_npi_brs_v lbnbv on upper(lbnbv.pmd_program_name)  = upper(oet .product) 
where oet.pmd_program_id is null
group by oet.product,lbnbv .pmd_program_id
order by oet.product
)
update origin_ems_transactions
set pmd_program_id = tem.pmd_program_id
from tem 
where origin_ems_transactions.product=tem.product;