MySQL更新Legal_Persons表percentual字段报错1241求助

报错原因 #

你写的SQL报错1241. Operand should contain 1 column(s)，是因为IN子查询返回了id_pessoas和COUNT(id_pessoas)两列，但IN运算符仅支持单列结果集，所以触发了这个错误。

解决方案 #

推荐用JOIN方式关联统计结果表来更新，既规避子查询的限制，又更高效：

1. 更新id_pessoas出现次数为1且percentual为null的记录 #

UPDATE `mydb`.`Legal_Persons` lp
JOIN (
    SELECT id_pessoas, COUNT(*) AS cnt
    FROM `mydb`.`Legal_Persons`
    WHERE percentual IS NULL
    GROUP BY id_pessoas
    HAVING cnt = 1
) AS cnt_tbl ON lp.id_pessoas = cnt_tbl.id_pessoas
SET lp.percentual = 100
WHERE lp.percentual IS NULL;

2. 更新id_pessoas出现次数为2且percentual为null的记录 #

假设需要给这类记录设置percentual为50（可根据实际需求修改数值）：

UPDATE `mydb`.`Legal_Persons` lp
JOIN (
    SELECT id_pessoas, COUNT(*) AS cnt
    FROM `mydb`.`Legal_Persons`
    WHERE percentual IS NULL
    GROUP BY id_pessoas
    HAVING cnt = 2
) AS cnt_tbl ON lp.id_pessoas = cnt_tbl.id_pessoas
SET lp.percentual = 50
WHERE lp.percentual IS NULL;

3. 一次性处理多种出现次数的更新 #

如果需要同时处理不同出现次数的记录，可以用CASE WHEN批量设置：

UPDATE `mydb`.`Legal_Persons` lp
JOIN (
    SELECT id_pessoas, COUNT(*) AS cnt
    FROM `mydb`.`Legal_Persons`
    WHERE percentual IS NULL
    GROUP BY id_pessoas
) AS cnt_tbl ON lp.id_pessoas = cnt_tbl.id_pessoas
SET lp.percentual = CASE 
    WHEN cnt_tbl.cnt = 1 THEN 100
    WHEN cnt_tbl.cnt = 2 THEN 50
    -- 可继续添加其他次数对应的percentual值
END
WHERE lp.percentual IS NULL;

说明 #

用JOIN方式更新的优势：MySQL不允许在UPDATE语句中直接引用正在修改的表的子查询（会引发一致性或锁定问题），通过JOIN预先统计好分组结果再关联更新，既规避了这个限制，又提升了执行效率。

内容的提问来源于stack exchange，提问作者Tecnico Rafael Amic