做数据变更的时候经常遇到这样的报错
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 < '2018-11-14 00:00:00'
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 < '2018-11-14 00:00:00'
AND loan_info.LENDING_TAG = 'SINAP2P'
) t
);
问题解决!