SQLite关联表更新异常：如何正确更新PersonTable的前缀后缀字段

问题根源 #

你原语句的核心错误在于子查询里重复引用了PersonTable，导致子查询与外层正在更新的行失去关联。子查询变成了独立查询所有FactType=42的记录，SQLite会默认取第一条结果，最终所有PersonTable行都被设置成同一个值。

正确的UPDATE语句 #

1. 更新NamePrefix（对应FactType=42）

这条语句会将对应PersonID的FactType=42的FactDetail，和原NamePrefix拼接（处理NULL情况），仅更新存在匹配记录的行：

UPDATE PersonTable
SET NamePrefix = TRIM(
    COALESCE(PersonTable.NamePrefix || ' ', '') || 
    (SELECT FactDetail FROM FactTable WHERE FactType = 42 AND OwnerID = PersonTable.PersonID)
)
WHERE EXISTS (
    SELECT 1 FROM FactTable WHERE FactType = 42 AND OwnerID = PersonTable.PersonID
);

• COALESCE：处理原NamePrefix为NULL的场景，避免出现NULL 这类无效拼接结果
• TRIM：清理拼接后可能出现的首尾空格
• WHERE EXISTS：确保只有存在对应Fact记录的行才会被更新，不会影响无匹配的行（比如PersonID1、4）

2. 更新NameSuffix（对应FactType=36）

同理，更新NameSuffix字段：

UPDATE PersonTable
SET NameSuffix = (SELECT FactDetail FROM FactTable WHERE FactType = 36 AND OwnerID = PersonTable.PersonID)
WHERE EXISTS (
    SELECT 1 FROM FactTable WHERE FactType = 36 AND OwnerID = PersonTable.PersonID
);

执行结果验证 #

执行第一条语句后，PersonTable结果：

执行第二条语句后，PersonTable结果：

关键说明 #

• 子查询直接从FactTable查询，通过OwnerID = PersonTable.PersonID与外层更新的行绑定，确保每个PersonID获取自己对应的记录
• WHERE EXISTS过滤掉无匹配Fact记录的行，避免不必要的更新操作
• 若存在同一PersonID对应多条同FactType的记录，SQLite会取第一条，符合你“通常每种FactType只有一条记录”的场景

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