mysql 1093 you can_解决MySQL报错[Err] 1093 - You can't specify target...

做数据变更的时候经常遇到这样的报错DELETE

FROM

ssq_contract_record

WHERE

LOAN_ID IN (

SELECT

ssq_contract_record.LOAN_ID

FROM

ssq_contract_record

LEFT JOIN loan_info ON ssq_contract_record.LOAN_ID = loan_info.ID

WHERE

ssq_contract_record.CREATE_TIME > '2018-10-30 00:00:00'

AND ssq_contract_record.CREATE_TIME 

AND loan_info.LENDING_TAG = 'SINAP2P'

);

[Err] 1093 - You can't specify target table 'XXX' for update in FROM clause

报错的意思是 你不能在update这张表的同时在from的子查询中出现这张表

解决办法很简单，外面再加一层查询“骗过”MySQL，

改写如下DELETE

FROM

ssq_contract_record

WHERE

LOAN_ID IN (

SELECT

t.loan_Id

FROM

(

SELECT

ssq_contract_record.LOAN_ID

FROM

ssq_contract_record

LEFT JOIN loan_info ON ssq_contract_record.LOAN_ID = loan_info.ID

WHERE

ssq_contract_record.CREATE_TIME > '2018-10-30 00:00:00'

AND ssq_contract_record.CREATE_TIME 

AND loan_info.LENDING_TAG = 'SINAP2P'

) t

);

问题解决!