目录

一、直接update

二、关联更新单列数据

三、关联更新多列数据

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

五、upsert

一、直接update

UPDATE table_nameSET column1 = value1, column2 = value2...., columnN = valueNWHERE [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;

来源地址：https://blog.csdn.net/chimchim66/article/details/128914001