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

最新推荐文章于 2023-04-19 07:31:59 发布

DBA之路

最新推荐文章于 2023-04-19 07:31:59 发布

阅读量429

点赞数

本文链接：https://blog.csdn.net/weixin_48154829/article/details/106395440

版权

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

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
	);

问题解决!