PostgreSQL 11按指定条件更新同表desc_ar/desc_en字段的问题

问题背景 #

使用PostgreSQL 11，有一张portal_courses表，字段包括：

• department_id：部门ID
• course_no：课程编号
• department_mother：母部门ID
• status：状态
• desc_ar：阿拉伯语描述
• desc_en：英语描述

现有数据示例：

department_id,    course_no,department_mother,status,desc_ar,      desc_en
51                4516      51                1,     testAR4516,   testEn4516
63                8542      51                1,     null,         null
28                8886      51                1,     null,         null
22                8552      51                1,     testAR8552,   testEn8552
60                1002      39                1,     testAR1002,   testEn1002
70                9856      70                1,     null,         null
71                8523      70                1,     testAR8523,   testEn8523

需求 #

1. 筛选出满足department_id = department_mother且desc_ar、desc_en均不为空的记录（作为基准数据）
2. 将所有department_mother与基准数据相同，且自身desc_ar、desc_en均为null的记录，更新为对应基准数据的desc_ar、desc_en值

仅需更新以下两条记录：

63                8542      51                1,     testAR4516,   testEn4516
28                8886      51                1,     testAR4516,   testEn4516

错误SQL分析 #

你尝试的SQL未成功，核心问题有两个：

UPDATE 
   portal_courses 
SET 
 desc_ar = p1.desc_ar ,desc_en=p1.desc_en
FROM 
   portal_courses  p1
    
WHERE 
  portal_courses.department_id = p1.department_mother
   and portal_courses.status='1' and portal_courses.desc_ar is  null and portal_courses.desc_en is  null
   and p1.desc_ar is not null and p1.desc_en is not null

1. 关联条件错误：待更新记录的department_mother应和基准记录的department_mother匹配，而非portal_courses.department_id = p1.department_mother
2. 未限定基准记录范围：缺少p1.department_id = p1.department_mother条件，会误匹配到非基准的其他有描述的记录（比如department_id=22的条目）

正确解决方案 #

步骤1：验证待更新记录（可选，避免误操作） #

先执行查询确认哪些记录会被更新，以及对应目标值：

SELECT 
    pc.*, p1.desc_ar AS target_desc_ar, p1.desc_en AS target_desc_en
FROM portal_courses pc
JOIN portal_courses p1 
    ON pc.department_mother = p1.department_mother
WHERE 
    pc.status = '1' 
    AND pc.desc_ar IS NULL 
    AND pc.desc_en IS NULL
    AND p1.department_id = p1.department_mother
    AND p1.desc_ar IS NOT NULL 
    AND p1.desc_en IS NOT NULL;

步骤2：执行更新操作 #

UPDATE portal_courses pc
SET 
    desc_ar = p1.desc_ar,
    desc_en = p1.desc_en
FROM portal_courses p1
WHERE 
    pc.status = '1' 
    AND pc.desc_ar IS NULL 
    AND pc.desc_en IS NULL
    AND pc.department_mother = p1.department_mother
    AND p1.department_id = p1.department_mother
    AND p1.desc_ar IS NOT NULL 
    AND p1.desc_en IS NOT NULL;

结果验证 #

执行更新后，目标记录的desc_ar和desc_en会被替换为对应基准记录（department_id=51）的值，其他记录不受影响，符合预期结果。

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