微慑信息网

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
)

 

 

 

本文标题:mysql无法在查询的同时对数据表进行更新
本文链接:
(转载请附上本文链接)
http://vulsee.com/archives/vulsee_2018/1226_7273.html
转载请附本站链接,未经允许不得转载,,谢谢:微慑信息网-VulSee.com » mysql无法在查询的同时对数据表进行更新
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

微慑信息网 专注工匠精神

访问我们联系我们