PostgreSQL中UPDATE关联表赋值避免误改列的方法

首先明确：只要UPDATE语句的SET子句只指定更新capital_type_id，就绝对不会修改capital字段。你之前遇到的capital变为null的情况，大概率是操作时的误写（比如不小心把SET的字段写成了capital），或者是其他未注意到的额外操作导致的，和你给出的那两段SQL本身的逻辑无关。

下面提供几种能安全完成capital_type_id赋值、且完全不会触碰capital字段的写法：

写法一：你已经验证过的关联更新 #

UPDATE tmp
SET capital_type_id = capital_type.capital_type_id
FROM capital_type
WHERE capital_type.type = tmp.capital;

这个写法通过FROM子句关联两张表，匹配类型后赋值，逻辑清晰，只会更新capital_type_id字段。

写法二：子查询赋值（更适合新手理解） #

UPDATE tmp
SET capital_type_id = (
    SELECT capital_type_id
    FROM capital_type
    WHERE capital_type.type = tmp.capital
);

直接通过子查询获取对应类型的ID值，赋值逻辑直观，同样不会修改capital字段。

写法三：避免未匹配行被设为null的安全写法 #

如果担心tmp表中存在capital值在capital_type表中没有对应项的行，导致capital_type_id被设为null，可以加上匹配判断：

UPDATE tmp
SET capital_type_id = capital_type.capital_type_id
FROM capital_type
WHERE tmp.capital = capital_type.type
AND EXISTS (
    SELECT 1
    FROM capital_type
    WHERE capital_type.type = tmp.capital
);

或者用子查询版本：

UPDATE tmp
SET capital_type_id = (
    SELECT capital_type_id
    FROM capital_type
    WHERE capital_type.type = tmp.capital
)
WHERE EXISTS (
    SELECT 1
    FROM capital_type
    WHERE capital_type.type = tmp.capital
);

这样只会更新那些在capital_type表中有对应类型的行，未匹配的行不会改动capital_type_id字段。

内容的提问来源于stack exchange，提问作者qwerty_99