- oracle
delete from bis_route_info where source_id in (select SOURCE_ID from bis_route_info group by SOURCE_ID having count(SOURCE_ID) > 1)
and uuid not in (select min(uuid) from bis_route_info group by SOURCE_ID having count(SOURCE_ID) > 1)
- mysql
由于myql执行和oracle一样的语句时,会报错:
[Err] 1093 - You can't specify target table 'bis_route_info' for update in FROM clause
所以,想了一个办法,可以先把需要删除的数据插入到例外一张临时表中,然后根据这张表的数据进行删除,sql语句如下:
--创建临时表
create table bis_route_temp (uuid varchar(40))
--将待删除的数据插入临时表
insert into bis_route_temp (uuid) select uuid from bis_route_info where source_id in (select SOURCE_ID from bis_route_info group by SOURCE_ID having count(SOURCE_ID) > 1)
and uuid not in (select min(uuid) from bis_route_info group by SOURCE_ID having count(SOURCE_ID) > 1)
--根据临时表的数据进行删除
delete from bis_route_info where uuid in(select uuid from bis_route_temp)