postgresql一个字段更新多条数据、多个字段更新多条数据

字段更新多条数据、多个字段更新多条数据"/>

postgresql一个字段更新多条数据、多个字段更新多条数据

开发过程中难免碰到修改数据的trouble，怎么快速更新多条数据？以下是postgresql实操：

1、需要更新同一字段值的多条数据

update pf_model_info 
set update_time = '20200528131400'
where model_id in ('83085dbda97b45699469c46c623d0258','a4b0038554bc4d1bb12a7bfb97ca01ab','1373ca9d87564b8c9a65151da8ebf13d') 
-- 所要更新字段的主键值

2、需要更新一个字段不同值的多条数据

update pf_model_info test set model_img_url = tmp.model_img_url 
from
(values
('9914b90cf7324570a4ac8f59fe335a7e',  '12'),
('df71d00abba54260a421d2f5e289d557',  '13'),
('90311d8fbb20411cb440b00abf04e5a6',	'15')
) 
as tmp (model_id,model_img_url) where test.model_id=tmp.model_id; 

• 延伸扩展
  这样就可以操作多个字段不同值多条数据的修改了 YES!

update pf_model_info test set model_img_url = tmp.model_img_url,model_name = tmp.model_name
from
(values
('9914b90cf7324570a4ac8f59fe335a7e',  '12', '测试模块1'),
('df71d00abba54260a421d2f5e289d557',  '13', '测试模块2'),
('90311d8fbb20411cb440b00abf04e5a6',	'15', '测试模块3')
) 
as tmp (model_id,model_img_url,model_name) where test.model_id=tmp.model_id; 

3、多表子查询更新

update aicare.zm_equipment_sensor wt
set community_id = rr_pid
from 
(
select equ.equ_id,equmunity_id,equ.equ_name,ho_pid
from aicare.zm_equipment_sensor equ 
left join aicare.zm_org_home ho on ho_id = equ_id and ho.status_flag = '1' 
where equ.status_flag = '1' and equ_id is not null 
) rr
where rr.equ_id = wt.equ_id

from关键字、更新字段不能加表别名