Delete duplicate records based on multiple columns

Question

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.

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?

I was playing around with this:

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?

Any ideas?

http://stackoverflow.com/questions/14124212/remove-duplicate-records-based-on-multiple-columns this helps? — argentum47, Nov 28 '14 at 13:54
That worked great @argentum47 can't believe I missed that when I was browsing — bnussey, Nov 28 '14 at 15:30

potashin · Accepted Answer · 2018-11-20T10:24:39.007

19

You can try the following approach:

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

Or pure sql:

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

edited Nov 20 '18 at 10:24

answered Feb 08 '16 at 01:44

potashin

1

If you have associated records that you want to delete as well, use `destroy_all` instead of `delete_all` – Sami Birnbaum Nov 19 '18 at 16:56

1 Answers1