MySQL 删除多余重复的记录,只保留一条

显示所有重复数据分组

SELECT 
    origin_id
FROM 
    topk88_product
GROUP BY origin_id 
HAVING count(*)>1


删除所有重复

DELETE FROM topk88_product
WHERE origin_id IN (
    SELECT 
        origin_id
    FROM 
        topk88_product
    GROUP BY origin_id 
    HAVING count(*)>1
);

提示:You can't specify target table 'topk88_product' for update in FROM clause;MySQL 不允许查询一个表的时候,又更新同一个表。解决办法是把要更新的几列数据查询出来做为一个临时表,然后筛选更新。


DELETE FROM topk88_product
WHERE origin_id IN (
    SELECT origin_id FROM(
        SELECT origin_id FROM topk88_product GROUP BY origin_id HAVING count(*)>1
    ) t
)


上面操作会把所有重复数据全部删除,可以设置只保留ID最小的那一条数据。

DELETE FROM topk88_product
WHERE origin_id IN (
  SELECT origin_id FROM(
    SELECT origin_id FROM topk88_product GROUP BY origin_id HAVING count(*)>1
  ) t
) 
AND id NOT IN (
  SELECT t2.min_id FROM(
    SELECT min(id) AS min_id FROM topk88_product GROUP BY origin_id HAVING count(*)>1
  ) t2
);


上面是正确的MySQL语句。


修改时间 2021-03-17

真诚赞赏,手留余香
赞赏
随机推荐
HTML5播放HLS流(.m3u8文件) Apache 跨域设置
Windows下如何开启 apache的 apache-status监控功能
如何使页面横屏
Git 重写历史,修改commit 的注释
macOS 终端清理“新建远程连接”的历史记录
一文看懂npm、yarn、pnpm之间的区别
扫描二维码发送模板消息
PHP 如何防止 CSRF 跨站域请求伪造
PHP 常用函数
网络协议入门
QQ号

微信联系我

夜间模式切换
回到顶部