PostgreSQL：如何正确查询并更新同表中父子记录的is_unique字段？

语句失效的原因 #

你的更新语句失效，核心问题在于**IN子查询中包含NULL值时的逻辑特性**：

• 当SELECT DISTINCT lr.parent_load_record_id FROM load_record lr的结果里存在NULL（比如有记录的parent_load_record_id是NULL），SQL中任何值与NULL做相等比较的结果都是UNKNOWN，而非TRUE或FALSE。
• 此时id IN (...)的逻辑等价于id = val1 OR id = val2 OR ... OR id = NULL，最后一个id = NULL的结果是UNKNOWN，导致整个IN条件的结果为UNKNOWN，WHERE子句只会筛选结果为TRUE的行，所以没有父记录被匹配到，更新自然不生效。
• 同理，你的无子女父记录查询返回0条，也是因为NOT IN遇到NULL时，整个条件结果为UNKNOWN，没有行被选中。

更优实现方式 #

推荐用EXISTS子查询或者LEFT JOIN来替代IN/NOT IN，它们对NULL的处理更符合直觉，性能也更稳定：

1. 更新有子记录的父记录 #

用EXISTS判断当前父记录是否存在子记录：

UPDATE load_record l
SET is_unique = false
WHERE l.parent_load_record_id IS NULL
AND EXISTS (
    SELECT 1
    FROM load_record lr
    WHERE lr.parent_load_record_id = l.id
);

2. 查询无子女的父记录 #

同样用EXISTS的否定形式：

SELECT 
    l.id,
    l.document_number,
    l.document_status,
    l.is_unique,
    l.parent_load_record_id
FROM load_record l
WHERE l.parent_load_record_id IS NULL
AND NOT EXISTS (
    SELECT 1
    FROM load_record lr
    WHERE lr.parent_load_record_id = l.id
);

3. 一次性完成所有更新（更高效） #

如果需要批量处理所有符合条件的记录，可以用一条语句搞定父记录和子记录的更新，避免多次操作：

UPDATE load_record l
SET is_unique = CASE
    -- 子记录直接设为false
    WHEN l.parent_load_record_id IS NOT NULL THEN false
    -- 有子记录的父记录设为false，无子女的保持true
    WHEN EXISTS (SELECT 1 FROM load_record lr WHERE lr.parent_load_record_id = l.id) THEN false
    ELSE true
END;

补充说明 #

• EXISTS子查询只要找到匹配的记录就会停止扫描，性能通常比IN更优，尤其是数据量较大时。
• 避免使用IN/NOT IN处理可能包含NULL的子查询结果，这是SQL中常见的陷阱。

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