如何修复MySQL报错You can't specify target table 'project' for update in FROM clause

MySQL 存在执行限制：进行UPDATE操作时，不允许在FROM子句的子查询中直接引用正在被更新的目标表（此处为project表），避免读写冲突导致数据一致性问题。

方案1：子查询外层包装临时派生表（改动最小） #

只需要在原有IN条件的子查询外层额外套一层查询，让MySQL将匹配的code值存入临时派生表后再做判断，即可绕过限制，调整后语句如下：

UPDATE project, id_card 
SET project.family_id = id_card.family_new 
WHERE project.PROJECT_ID = id_card.project 
AND id_card.code IN (
    SELECT code FROM (
        SELECT DISTINCT code FROM id_card ic
        INNER JOIN project p ON p.PROJECT_ID = ic.project 
            AND p.current_id_card_version = ic.version 
        WHERE ic.id_card_status = 'ARCHIVED'
    ) AS temp_archive_code
);

改动说明 #

• 新增的外层SELECT code FROM (...) AS temp_archive_code会让MySQL先生成临时结果集，不会直接和正在更新的project表产生关联，完全兼容原有逻辑。
• 注意临时派生表必须设置别名，否则会触发语法错误。

方案2：多表JOIN写法（性能更优） #

如果数据量较大，推荐改用显式JOIN的UPDATE写法，执行效率更高：

UPDATE project p
INNER JOIN id_card ic ON p.PROJECT_ID = ic.project
INNER JOIN (
    SELECT DISTINCT ic2.code
    FROM id_card ic2
    INNER JOIN project p2 ON p2.PROJECT_ID = ic2.project 
        AND p2.current_id_card_version = ic2.version 
    WHERE ic2.id_card_status = 'ARCHIVED'
) AS temp_archive ON ic.code = temp_archive.code
SET p.family_id = ic.family_new;

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