微慑信息网

mysql无法在查询的同时对数据表进行更新

mysql真的很坑啊..

[Err] 1093 – You can’t specify target table ‘proxylist’ for update in FROM clause

网上查询了下 mysql不支持在更新表(update、delete)的时候对表进行查询。。。

只能通过临时表来操作,然后删掉临时表。。。

也有个稍微简单的办法,绕过:

delete from proxylist where proxy_info in
(
select proxy_info from
(
select proxy_info from proxylist
group by proxy_info having count(proxy_info)>1
) as temp
) ;

在mysql中删除重复项:

select * from proxylist where proxy_info in
(
select proxy_info from
(
select proxy_info from proxylist
group by proxy_info having count(proxy_info)>1
) as temp
)
and id not in (
select min(id) as id from proxylist
group by proxy_info having count(proxy_info)>1)

通过min(id)取重复数据的最小ID,达到删除重复项的目的….

删除的时候,又要通过as temp来绕过了。。。

delete from proxylist where proxy_info in
(
select proxy_info from
(
select proxy_info from proxylist
group by proxy_info having count(proxy_info)>1
) as temp1
)
and id not in (
select id from(
select min(id)  as id from proxylist
group by proxy_info having count(proxy_info)>1) as temp2
)

 

 

 

赞(0) 打赏
转载请附本站链接,未经允许不得转载,,谢谢:微慑信息网-VulSee.com » mysql无法在查询的同时对数据表进行更新

评论 抢沙发

微慑信息网 专注工匠精神

微慑信息网-VulSee.com-关注前沿安全态势,聚合网络安全漏洞信息,分享安全文档案例

访问我们联系我们

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续提供更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫打赏

微信扫一扫打赏

登录

找回密码

注册