MySQL 删除表中重复数据，保留最小ID值

最新推荐文章于 2024-08-07 14:51:18 发布

一点生活_cool

最新推荐文章于 2024-08-07 14:51:18 发布

阅读量908

点赞数

本文链接：https://blog.csdn.net/Sulong_cool/article/details/81110804

版权

使用SQL语句，删除表中重复的项，保留最小的ID；

在表 tabel 1中，存在大量重复数据，需要删除重复项，且保存 ID 为最小的那条记录。

tabel 1表中【jzmdid】数据重复
ID	jzmdid	aac002	aac003	yj_month
10001	3002	--	--	201805
10002	3002	--	--	201805
10003	3003	--	--	201805
10004	3004	--	--	201805

[SQL] 
delete from table1  WHERE jzmdid in  
( select jzmdid from table1 where yj_month='201805' GROUP BY jzmdid
  HAVING COUNT(jzmdid) >1)  
  and id not in 
( select min(id) as minid
  from table1 where  yj_month='201805' group by jzmdid having count(jzmdid)>1 ) ;

[Err] 1093 - You can't specify target table 'table1' for update in FROM clause

[Err] 1093 - You can't specify target table 'table1' for update in FROM clause

分析：Mysql 中Delete和Update 语句，不允许子查询中出现update和delete要操作表

解决方法：给子查询外面再加一个查询

1、删除重复记录,保存 ID最小的一条

-- 1、删除重复记录,保存Id最小的一条
 delete from table1  WHERE yj_month='201805' and jzmdid in  
  ( select * from (
    select jzmdid from table1 where yj_month='201805' GROUP BY jzmdid 
      HAVING COUNT( jzmdid ) >1) s
       ) and id not in 

        ( select minid from (
           select min(id) as minid
            from table1
             where  yj_month='201805' 
              group by jzmdid having count(jzmdid )>1 ) b) ; 

受影响的行: 431250
时间: 106.209s