AS/400中通过SELECT批量更新列时遇到子查询返回多行的问题

嘿，我一看你这个问题就知道是怎么回事了——虽然你说MRN和CaseNo都是主键，但你的SQL写法里有两个关键问题导致了“子查询返回多行”的报错，我给你一步步拆解并修正：

首先，问题出在哪？ #

你原来的SET子句里的子查询：

SELECT FName || ' ' || Mname || ' ' || LName
FROM QS36F.PATIENT
WHERE fname <> '' AND mname <> '' AND lname <> ''

这个查询完全没有和外层的MD001表做关联！它会把Patient表里所有满足名字三个字段都非空的记录全部查出来，那肯定返回不止一行，这就是报错的直接原因。

另外你WHERE子句里的m.MRN = (SELECT caseno FROM qs36f.patient WHERE caseno = m.MRN) 这部分完全是多余的，相当于“验证MRN等于它自己”，毫无意义，反而把逻辑搞复杂了。

给你两种修正后的写法，随便选哪种都可以 #

写法一：关联子查询（保持你原来的子查询风格）

UPDATE QS36F.MD001 m 
SET NAME = (
    -- 这里直接关联p.caseno = m.MRN，因为都是主键，只会返回一行
    SELECT FName || ' ' || Mname || ' ' || LName
    FROM QS36F.PATIENT p
    WHERE p.caseno = m.MRN
      AND p.fname <> '' 
      AND p.mname <> '' 
      AND p.lname <> ''
)
-- 用EXISTS确保只更新Patient表中有对应有效记录的行，避免更新成NULL
WHERE EXISTS (
    SELECT 1 FROM QS36F.PATIENT p
    WHERE p.caseno = m.MRN
      AND p.fname <> '' 
      AND p.mname <> '' 
      AND p.lname <> ''
);

写法二：UPDATE JOIN（更直观，性能也可能更好）

AS/400的DB2支持直接用JOIN来更新，这种写法逻辑更清晰，不容易出错：

UPDATE QS36F.MD001 m
SET NAME = p.FName || ' ' || p.Mname || ' ' || p.LName
FROM QS36F.PATIENT p
-- 直接关联两个表的主键，确保一对一匹配
WHERE m.MRN = p.caseno
  AND p.fname <> '' 
  AND p.mname <> '' 
  AND p.lname <> '';

额外提几个小建议 #

• 如果你担心中间名可能为空（虽然你加了mname<>''的过滤），可以用COALESCE来避免拼接出多余的空格，比如把拼接部分改成：

  p.FName || COALESCE(' ' || p.Mname, '') || ' ' || p.LName
  

  这样如果中间名为空，就不会出现两个连续空格。
• 虽然你说MRN和CaseNo是主键，但如果还是担心数据有问题，可以跑个查询验证一下有没有重复匹配：

  SELECT m.MRN, COUNT(*)
  FROM QS36F.PATIENT p
  JOIN QS36F.MD001 m ON p.caseno = m.MRN
  GROUP BY m.MRN
  HAVING COUNT(*) > 1;
  

  如果这个查询返回结果，说明你的主键可能有问题，或者数据真的有重复。

备注：内容来源于stack exchange，提问作者user10191234