PostgreSQL跨表多字段匹配更新is_valid字段实现方法

测试数据 #

复现问题的测试表与初始化数据如下：

• table1 建表及插入语句：

create table table1 (category_id int,unit varchar,is_valid bool);

insert into table1 (category_id, unit, is_valid)
VALUES (1, 'a', true), (2, 'z', true);

table1初始数据：

• table2 建表及插入语句：

create table table2 (category_id int,unit varchar);

insert into table2 (category_id, unit)
values(1, 'a'),(1, 'b'),(1, 'c'),(2, 'd'),(2, 'e');

table2初始数据：

需求逻辑 #

更新table1的is_valid字段：当某行的category_id、unit组合在table2中无匹配记录时，将该行is_valid设为false，存在匹配则保留原有值。
预期效果：(1,a)在table2存在匹配，is_valid保持true；(2,z)无匹配，is_valid更新为false。

正确实现语句 #

推荐写法：NOT EXISTS 关联判断 #

这是最稳妥、性能最好的写法，不会受字段NULL值影响，语义也最直观：

UPDATE table1 
SET is_valid = false 
WHERE NOT EXISTS (
    SELECT 1
    FROM table2
    WHERE 
        table2.category_id = table1.category_id
        AND table2.unit = table1.unit
);

语句逻辑：遍历table1每一行时，子查询检查table2中是否存在同category_id、同unit的记录，不存在则命中更新条件，将is_valid置为false。

可选写法：NOT IN 多列匹配 #

PostgreSQL支持多列同时做IN判断，写法更简洁，但要注意子查询返回的字段不能有NULL值，否则会出现判断逻辑失效：

UPDATE table1
SET is_valid = false
WHERE (category_id, unit) NOT IN (
    SELECT category_id, unit FROM table2
);

注意：如果table2中category_id或unit存在NULL值，NOT IN的判断结果会全部返回unknown，导致没有任何行被更新，生产环境优先选择NOT EXISTS方案。

执行结果验证 #

执行更新语句后查询table1，可得到符合预期的结果：

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