mysql、oracle删除重复数据

1. 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)

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)