sql 删除重复记录

删除report表中重复的url；

delete from report WHERE site=6 AND url in(select url from report where site=6 group by url having count(*) >1)

出现错误：You can't specify target table 'report' for update in FROM clause
错误提示，不能先select出同一表中的某些值，再update这个表(在同一语句中)
解决方法如下：(建一个临时表，之后删除)

create table tmp as select url as col1 from report WHERE site=6 group by url having count(*) >1;
delete from report where url in (select col1 from tmp);
drop table tmp;