postgresql update from

1，update   from   关联表的更新

　　update table a set name=b.name from table B b  where a.id=b.id;

　　update test set info=tmp.info from (values (1,'new1'),(2,'new2'),(6,'new6')) as tmp (id,info) where test.id=tmp.id;  

 

 

2 update from 

　　update TABLEA a 

　　set  update_time=now,

　　　　name=case when b.name is not null then d.name else a.name end,

　　　　age=case when b.age is not null then d.age else a.age end

 

　　from TABELEB b   where a.id=b.id;

 

3.实际应用举例：

 

update dp1.m_task mt set update_date=now() , is_delegated=case when d.is_delegated is not null then d.is_delegated else mt.is_delegated end , priority=case when d.priority is not null then d.priority else mt.priority end , status=case when d.status is not null then d.status else mt.status end, task_group_id=case when d.task_group_id is not null then d.task_group_id else mt.task_group_id end , task_name=case when d.task_name is not null then d.task_name else mt.task_name end , team_id=case when d.team_id is not null then d.team_id else mt.team_id end 

from (

select 100 as client_id,

is_delegated::integer,priority::integer,status::smallint,task_code::character varying,task_group_id::integer,task_name::character varying,team_id::integer

from (

select rowindex,is_delegated,priority,status,task_code,task_group_id,task_name,team_id 

from ext.crosstab (

'

select rowindex,column_name,column_value

from field_fixed_detail_table a

',

'

select column_name from fixed_column_table

'

)

as ct ( rowindex integer,is_delegated varchar,priority varchar,status varchar,task_code varchar,task_group_id varchar,task_integrate_code varchar,task_name varchar,team_id varchar)

) as fixed

) as d

where mt.task_code=d.task_code;