Rails：根据多个列删除重复的记录 [英] Rails: Delete duplicate records based on multiple columns

问题描述

在我们的系统中，我们从外部数据库运行每小时进口。由于导入脚本中的错误，现在有一些重复的记录。

In our system we run hourly imports from an external database. Due to an error in the import scripts, there are now some duplicate records.

重复被视为其中任何记录具有相同的：legacy_id 和：公司。

A duplicate is deemed where any record has the same :legacy_id and :company.

我可以运行哪些代码来查找和删除这些重复项？

What code can I run to find and delete these duplicates?

我正在玩这个：

Product.select(:legacy_id,:company).group(:legacy_id,:company).having("count(*) > 1")

似乎返回了一些重复的内容，但我不知道如何从那里删除？

It seemed to return some of the duplicates, but I wasn't sure how to delete from there?

任何想法？

推荐答案

您可以尝试以下方法：

Product.group_by{|x| [x.legacy_id, x.company]}
       .flat_map{|_,x| x.drop(1)}
       .delete_all

或纯ActiveRecord：

Or pure ActiveRecord:

Product.where.not(id: Product.group(:legacy_id, :company).pluck('min(products.id)'))
       .delete_all

或纯sql：

delete from products
where id not in ( 
   select min(p.id) from products p group by p.legacy_id, p.company
)

这篇关于Rails：根据多个列删除重复的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！