CODEWITHSUNDEEP
rubypostgresqlruby-on-rails-4activerecord
Jess
Jess

Reputation: 11

Remove duplicated records based on pair of columns?

I have a table with the folowwing columns:

id
app_id
tag_id
created_at
updated_at

The pair app_id and tag_id should be uniq. How to remove oldest duplicated records based on updated_at column to determine "oldest" and pair (app_id, tag_id) to determine "duplicated"?

Upvotes: 1

Views: 82

Answers (1)

Aetherus
Aetherus

Reputation: 8888

 ActiveRecord::Base.connection.execute <<-SQL
   delete from my_table where id in (
     select t1.id
     from my_table t1
     inner join (
       select app_id, tag_id, min(updated_at) as oldest
       from my_table
       group by app_id, tag_id
     ) t2 
     on t1.app_id = t2.app_id 
     and t1.tag_id = t2.tag_id
     and t1.updated_at = t2.oldest
   )
 SQL

Replace my_table with your actual table name.

Upvotes: 2

Related Questions