按条件删除记录时报You can’t specify target table for update in FROM clause错误解决方法
核心概念:
mysql中,不能先select一个表的记录,在按此条件进行更新和删除同一个表的记录。解决办法是,将select得到的结果,再通过中间表select一遍,这样就规避了错误,这个问题只出现于mysql,mssql和oracle不会出现此问题。
自己实例一:
如下业务场景,ecs_order_shipping表里面记录了每一个订单的配送流转记录,type从11,12,21,22,23这么递进,
按理说每个订单的每一个type就出现一次,因为系统bug造成type为11、12的记录,都出现了两次或多次。对于每一个订单,如果它有两条type等于11的ecs_order_shipping记录,那么只保留第一条,其他的删除。
一、先查出来那些要被删除的记录的id组合
#查出type为11的全部记录
select * from ecs_order_shipping where type = 11;
#分组后,记录少了,说明有重复的记录
select * from ecs_order_shipping where type = 11 group by order_sn;
#这样筛选出那些重复的订单
select * from ecs_order_shipping where type = 11 group by order_sn having count(*) >1;
#从筛选的重复订单中,得到id最大的
select max(id) from ecs_order_shipping where type=11 group by order_sn having count(*) >1;
二、根据这些id组合,删除他们
delete from ecs_order_shipping where id in
(
select max(id) from ecs_order_shipping where type=11 group by order_sn having count(*) >1
)
这样就会报错!!!Error : You can't specify target table 'ecs_order_shipping' for update in FROM clause
三、如上,修改sql语句如下即可
/****每个订单type等于11,12的记录,出现了很多重复的,要删除重复项,只留下最早的那个***/
delete from ecs_order_shipping where id in
(
select a.id from
(
select max(id) as id from ecs_order_shipping as b where b.type=11 group by b.order_sn HAVING count(*) >1
)as a
)
delete from ecs_order_shipping where id in
(
select a.id from
(
select max(id) as id from ecs_order_shipping as b where b.type=12 group by b.order_sn HAVING count(*) >1
)as a
)
mysql中,不能先select一个表的记录,在按此条件进行更新和删除同一个表的记录。解决办法是,将select得到的结果,再通过中间表select一遍,这样就规避了错误,这个问题只出现于mysql,mssql和oracle不会出现此问题。
自己实例一:
如下业务场景,ecs_order_shipping表里面记录了每一个订单的配送流转记录,type从11,12,21,22,23这么递进,
按理说每个订单的每一个type就出现一次,因为系统bug造成type为11、12的记录,都出现了两次或多次。对于每一个订单,如果它有两条type等于11的ecs_order_shipping记录,那么只保留第一条,其他的删除。
一、先查出来那些要被删除的记录的id组合
#查出type为11的全部记录
select * from ecs_order_shipping where type = 11;
#分组后,记录少了,说明有重复的记录
select * from ecs_order_shipping where type = 11 group by order_sn;
#这样筛选出那些重复的订单
select * from ecs_order_shipping where type = 11 group by order_sn having count(*) >1;
#从筛选的重复订单中,得到id最大的
select max(id) from ecs_order_shipping where type=11 group by order_sn having count(*) >1;
二、根据这些id组合,删除他们
delete from ecs_order_shipping where id in
(
select max(id) from ecs_order_shipping where type=11 group by order_sn having count(*) >1
)
这样就会报错!!!Error : You can't specify target table 'ecs_order_shipping' for update in FROM clause
三、如上,修改sql语句如下即可
/****每个订单type等于11,12的记录,出现了很多重复的,要删除重复项,只留下最早的那个***/
delete from ecs_order_shipping where id in
(
select a.id from
(
select max(id) as id from ecs_order_shipping as b where b.type=11 group by b.order_sn HAVING count(*) >1
)as a
)
delete from ecs_order_shipping where id in
(
select a.id from
(
select max(id) as id from ecs_order_shipping as b where b.type=12 group by b.order_sn HAVING count(*) >1
)as a
)