MySQL中WITH RECURSIVE递归查询结合UPDATE报错1241的问题排查

问题原因 #

触发#1241错误的核心原因是：IN运算符要求右侧子查询仅返回1列数据（即需要匹配的jobholder_uid列），但你当前递归CTE的最终SELECT返回了jobholder_uid、positionmgr_uid、position_level、bu_div共4列，数据库无法判断匹配依据列，因此抛出列数不匹配错误。
额外说明：递归逻辑中不需要查询bu_div字段，该字段是更新目标，不参与递归筛选逻辑。

解决代码 #

写法1：简化IN子查询版 #

UPDATE positions SET bu_div = 'Customer Services' WHERE jobholder_uid IN
(
WITH RECURSIVE mgmt_levels AS ( 
    SELECT jobholder_uid FROM positions 
    WHERE positionmgr_uid = 'foo.bar@company.com'
    UNION ALL
    SELECT p.jobholder_uid FROM positions p 
    INNER JOIN mgmt_levels ml ON ml.jobholder_uid = p.positionmgr_uid 
)
SELECT jobholder_uid FROM mgmt_levels
);

写法2：多表关联更新版（兼容性更好，适配低版本MySQL） #

WITH RECURSIVE mgmt_levels AS ( 
    SELECT jobholder_uid FROM positions 
    WHERE positionmgr_uid = 'foo.bar@company.com'
    UNION ALL
    SELECT p.jobholder_uid FROM positions p 
    INNER JOIN mgmt_levels ml ON ml.jobholder_uid = p.positionmgr_uid 
)
UPDATE positions p
INNER JOIN mgmt_levels ml ON p.jobholder_uid = ml.jobholder_uid
SET p.bu_div = 'Customer Services';

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