postgresql update from

时间:2024-04-14 22:37:18

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;