sinat_19250161
2015-05-18 07:03
采纳率: 0%
浏览 5.3k

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
求大神,怎么解决

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • guwei4037 2015-05-18 07:10
    评论
    解决 无用
    打赏 举报
  • danielinbiti 2015-05-18 07:18

    改成

     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;
    
    评论
    解决 无用
    打赏 举报
  • libitum_1 2015-05-18 08:27

    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最大的一条,自己执行过 没有问题!
    
    评论
    解决 无用
    打赏 举报
  • 逝_去_的_光_阴 2015-05-18 08:47

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

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题