同表更新时使用SELECT子查询抛出#1093错误求助

错误原因 #

MySQL的UPDATE/DELETE语句里，不允许在WHERE子句的子查询中直接引用正在被更新的目标表——引擎出于数据一致性校验的限制，会抛出这个错误。你的原语句中，work_info既是要更新的表，又在子查询里被直接引用，触发了这个限制。

解决方案1：嵌套子查询为临时表 #

把内层查询包装成临时表，让MySQL先计算出临时结果，再用该结果过滤要更新的数据，避开直接引用目标表的问题：

UPDATE work_info as info1
SET info1.status_id = 1
WHERE info1.info_id IN (
    SELECT info_id FROM (
        SELECT info2.info_id
        FROM work_info as info2
        WHERE info2.info_id IN (
            SELECT MAX(info3.info_id)
            FROM work_info as info3
            GROUP BY info3.user_license_id
        )
        AND info2.status_id = 5
    ) AS temp_table
);

解决方案2：改用JOIN语句（更高效） #

用JOIN替代多层IN子查询，既能避开错误，通常查询性能也更优：

UPDATE work_info as info1
JOIN (
    SELECT MAX(info3.info_id) AS max_info_id
    FROM work_info as info3
    GROUP BY info3.user_license_id
) AS temp_max ON info1.info_id = temp_max.max_info_id
SET info1.status_id = 1
WHERE info1.status_id = 5;

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