mysql的1093错误You can‘t specify target table ‘t‘ for update in FROM clause 简单解决

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

原创最新推荐文章于 2023-04-19 07:31:59 发布 · 1.1w 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

日志

当前文章被收录于：

日志

434 篇文章 6 人学习

订阅专栏查看详情

当前文章被以下社区和专栏收录：

本文介绍如何在MySQL中进行复杂的删除操作，包括解决自引用表的删除问题、删除指定条件下的子记录、以及如何删除重复记录同时保留特定条件下的记录等。通过实际案例展示了SQL语句的正确使用方式。

mysql>delete from twhere id in (select idfrom t where id < 5);

ERROR1093 (HY000): You can't specify target table 't' for update in FROM clause

改为下面就OK

delete from t where id in( select * from (select idfrom twhere id <5) tmp );

主句( select * from (从句) temp) )

set @id=7748;
select * from tb_iot where id=@id;
delete from tb_iot where pid in (        select * from (select id from tb_iot where pid=@id and model_id=170)    tmp   ); #智能设备集合 先删子级
DELETE FROM tb_iot  where pid=@id or id=@id; #再删本级及自己

删除重复，但保留最小id项。

DELETE
FROM
file
WHERE
fileaddress IN (
select * from ((
SELECT
fileaddress
FROM
file
GROUP BY
fileaddress
HAVING
count(fileaddress) > 1
) a)
)
AND id NOT IN (
select * from ((
SELECT
min(id)
FROM
file
GROUP BY
fileaddress
HAVING
count(fileaddress) > 1
) b)
)