sql删除同一个表的重复数据

数据如下,表名video,发现vid和section_id重复的数据很多,要删除重复的数据只保留一条

+----------+-----------+------------+
| video_id | vid       | section_id |
+----------+-----------+------------+
|    10908 | 284478110 |  528238111 |
|    10909 | 284478110 |  528248111 |
|    10884 | 278748110 |  528048111 |
|    10885 | 278758110 |  528058111 |<-重复
|    54155 | 278758110 |  528058111 |<-重复
|    10886 | 278768110 |  528068111 |
|    54156 | 278768110 |  528068111 |
|    10887 | 278778110 |  528078111 |
|    10888 | 282228110 |  528078111 |
|    54157 | 278778110 |  528078111 |
|    54158 | 282228110 |  528078111 |
|    10889 | 282148110 |  528088111 |
|    54159 | 282148110 |  528088111 |
|    10890 | 282158110 |  528098111 |
|    54160 | 282158110 |  528098111 |
|    10891 | 282168110 |  528108111 |
|    54161 | 282168110 |  528108111 |
|    10892 | 282178110 |  528118111 |
|    10893 | 282238110 |  528118111 |
|    54162 | 282178110 |  528118111 |
+----------+-----------+------------+

如果要保留小id的数据:

delete a from video a,video b where a.video_id>b.video_id and a.vid=b.vid and a.section_id=b.section_id

eg.

update ms_video a,ms_video b set a.status=0 where a.section_id=b.section_id and a.vid=b.vid and a.`status`=1 and b.`status`=1 and a.video_id<b.video_id; //video_id 为自增id
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容