mysql 1093解决方法_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