PostgreSQL update/delete/upsert关联更新字段数据
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
目录
一、直接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;