PostgreSQL update多张表关联查询更新_pg update 多表关联更新

场景

• 两张表，一张党员表，一张党组织表
• 党员表里冗余了一个党组织名称，但是党组织更新时，未更新党员表
• 现在需要使用SQL，将党员表里的冗余党组织名称修改正确
• PostgreSQL的多表更新语法，与MySQL、Oracle不太一样

问题解决

• 为了防止更新出错，先把党员表复制一份，使用复制表执行更新SQL操作
• 无论是生产环境，还是测试环境，数据安全都是很重要的，无论是更新还是删除，一定要慎重，否则造成无法挽回的损失，就只能跑路了（口嗨）
• 使用复制表操作，并验证结果，没问题后，再去原表操作（建议操作前再备份一次）

update后只能单张表

• 像之前的 update 多张表，会报错

UPDATE party_member_copy1 t1,
party_organ_structure t2 
SET party_organ_name = t2.NAME 
WHERE
	t1.party_organ_id = t2.ID 
	AND t2.record_status = 0
> ERROR:  syntax error at or near ","
LINE 1: UPDATE party_member_copy1 t1,
                                    ^
1
2
3
4
5
6
7
8
9

set不能使用别名

• set 后面没法跟别名，即使前面定义了别名也没法使用

UPDATE party_member_copy1 t1 
SET t1.party_organ_name = t2.NAME 
FROM
	party_organ_structure t2
	where t1.party_organ_id = t2.id and t2.record_status = 0
> ERROR:  column "t1" of relation "party_member_copy1" does not exist
LINE 2: SET t1.party_organ_name = t2.NAME 
            ^
1
2
3
4
5
6
7
8

正确写法使用from

• 前面定义的别名，from后面是可以使用的
• 定义别名，加不加 as 没影响的

-- 更新copy表
UPDATE party_member_copy1 t1 
SET party_organ_name = t2.NAME 
FROM
	party_organ_structure t2 
WHERE
	t1.party_organ_id = t2.ID 
	AND t2.record_status = 0;
1
2
3
4
5
6
7
8

验证

• 执行后可以使用查询验证下，是否更新正确
• 更新出错时，会更新0条数据或全部数据，或者更新为某一个值

-- copy表 与 党组织表 名称对比
SELECT
	t1.party_organ_id,
	t1.party_organ_name,
	t2.ID,
	t2.NAME 
FROM
	party_member_copy1 t1,
	party_organ_structure t2 
WHERE
	t1.party_organ_id = t2.ID 
	AND t2.record_status = 0;
-- 原表 与 党组织表 名称对比
SELECT
	t1.party_organ_id,
	t1.party_organ_name,
	t2.ID,
	t2.NAME 
FROM
	party_member t1,
	party_organ_structure t2 
WHERE
	t1.party_organ_id = t2.ID 
	AND t2.record_status = 0;
-- copy 表 和原表对比
SELECT
	t1.ID,
	t1.party_organ_id,
	t1.party_organ_name,
	t2.party_organ_name 
FROM
	party_member t1,
	party_member_copy1 t2 
WHERE
	t1.ID = t2.ID;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

• 查看发现，copy表里的党组织名称，已更新成功，与党组织表名称一致

最终SQL

• 再次提醒，数据安全，先备份再操作
• 一下为更新正式表SQL

-- 更新正式表
UPDATE party_member t1 
SET party_organ_name = t2.NAME 
FROM
	party_organ_structure t2 
WHERE
	t1.party_organ_id = t2.ID 
	AND t2.record_status = 0;
1
2
3
4
5
6
7
8