问题描述

Mysql删除重复数据只保留一条

Mysql删除重复数据只保留一条，我的sql这样的
DELETE from t_patient WHERE pa_iid IN (select pa_iid from t_patient group by pa_vname having count(pa_vname)>1)
但是报错了：
[Err] 1093 - You can't specify target table 't_patient' for update in FROM clause
求大神，怎么解决

解决方案

参考下这个：http://blog.csdn.net/softwave/article/details/3890576

解决方案二：

改成

 DELETE from t_patient WHERE pa_iid IN (
 select pa_iid from (select pa_iid from t_patient group by pa_vname having count(pa_vname)>1) t )

或者

 create table tmp as select min(pa_iid) as col1 from t_patient group by pa_vname ;
delete from t_patient where pa_iid not in (select col1 from tmp);
drop table tmp;

解决方案三：

DELETE
FROM
mp USING MusicTopMap AS mp,
(SELECT
MIN(id) AS id,
musicId,
top,
COUNT(top) AS count_
FROM
MusicTopMap
GROUP BY musicId,
top
HAVING COUNT(top) > 1) tmp
WHERE mp.musicId = tmp.musicId
AND mp.top = tmp.top
AND mp.id > tmp.id

参考下这个  我这个是MYSQL数据库，留下的是ID最大的一条，自己执行过 没有问题！

解决方案四：

分2条sql吧，先查出所有需要删除的id，然后再删除