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,
^
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
^
正确写法使用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;
验证
执行后可以使用查询验证下,是否更新正确
更新出错时,会更新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;
查看发现,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;