mysql5.7报错1093问题解决

今日在清理数据库多余数据的时候碰到一个问题，delete中使用where 子查询竟然报错1093

经过查阅资料后，发现mysql中，在更新修改一张表数据的时候，子查询中不能是一样的表名，需要起别名，话不多说，直接贴原始sql

DELETE  
FROM
    zz_resume_score_copy1 
WHERE
    `id` IN (
    SELECT
        `id` 
    FROM
        zz_resume_score_copy1 
    WHERE
        resume_type = 1 
    AND `year` = 2020 
    AND `resume_id` IN ( SELECT `id` FROM zz_resume_proposal_copy1 WHERE `year` = 2020 AND out_status = 0 ))

然后就出现了一下报错：

1093 - You can't specify target table 'zz_resume_score_copy1' for update in FROM clause

将sql修改修改后，正常运行，代码如下

DELETE 
FROM
    zz_resume_score_copy1 
WHERE
    `id` IN (
    SELECT
        z.`id` 
    FROM
        (
        SELECT
            `id` 
        FROM
            zz_resume_score_copy1 
        WHERE
            resume_type = 1 
            AND `year` = 2020 
        AND `resume_id` IN ( SELECT `id` FROM zz_resume_proposal_copy1 WHERE `year` = 2020 AND out_status = 0 )) z 
    )

其实，只要在子查询外再嵌套一层就好了，只要保证不是同一个表名就OK