PostgreSQL update/delete/upsert关联更新字段数据

一、直接update

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

示例：
UPDATE table1 SET ADDRESS = 'china', people_cnt=3;

二、关联更新单列数据

update ori_table_name a set col = (select col from new_table b where a.rel_col=b.rel_col);

update 要更新的表 a set 要修改的字段 = （select 该字段新数据 from 要关联的表 b where a.关联字段=b.关联字段);

示例：

update table1 a set address = 

(select address from table2 b where a.id=b.id);

update table1 a set people_cnt = 

(select max(people_cnt) from table2 b where a.id=b.id);

三、关联更新多列数据

update ori_table_name a set (col1,col2,col3) = (select col1,col2,col3 from new_table b where a.rel_col=b.rel_col);

update 要更新的表 a set (要修改的字段1、2、3） = （select 该字段1、2、3新数据 from 要关联的表 b where a.关联字段=b.关联字段);

示例：

update table1 a set (address,people_cnt) = 

(select address,people_cnt from table2 b where a.id=b.id);

四、多表关联查询并且delete删除指定表数据

DELETE FROM table_name WHERE [condition];

示例：

DELETE FROM table1 a using table2 WHERE a.id=b.id and address='china'; 

五、upsert

数据有变更会更新数据，主键id有新增会自动新增

insert into target_table  (id,address,people_cnt)

select id,address,people_cnt

from source_table 

where {condition}

ON CONFLICT (id) DO UPDATE

set id=EXCLUDED.id, address=EXCLUDED.address,people_cnt=EXCLUDED.people_cnt;