CODEWITHSUNDEEP
sqlpostgresqlduplicates
Tisha
Tisha

Reputation: 857

How do I delete one row for two duplicate entries in a postgres table?

The two rows have all same columns except a timestamp column - created_at

I just want to retain one of these rows - doesn't matter which.

This is how I am able to select one of each of these duplicate rows that I can delete on the basis of the created_at column which has the lesser value. 

 select e.id, e.user_id, e.r_needed, e.match, e.status, e.stage, e.created_at 
    from employee e, employee e2 where e.id=e2.id and e.user_id=e2.user_id and 
     e.status = 12 and e2.status=12 and e.stage=false and e2.stage=false and 
     e.match=true and e2.match=true and e.user_id=12 and e.r_needed=true and e2.r_needed=true
     and e.created_at<e2.created_at and DATE(e.created_at)='2015-10-08';

However, cannot figure how I can delete this row such that both of the duplicates do not get deleted and only the ones that are selected above do?

Basically, I want to delete all rows that match the columns in my select query above and the row which has the lesser value for created_at.

My table has no primary key or unique key.

Upvotes: 2

Views: 936

Answers (1)

dnoeth
dnoeth

Reputation: 60462

You can use a Correlated Subquery instead of a join:

select * from employee e
where exists
 ( select * from employee e2 
   where e.id=e2.id and e.user_id=e2.user_id 
     and e.status = 12 and e2.status=12 and e.stage=false 
     and e2.stage=false and e.match=true and e2.match=true 
     and e.user_id=12 and e.r_needed=true and e2.r_needed=true
     and e.created_at<e2.created_at 
     and DATE(e.created_at)='2015-10-08'
 );

If this returns the duplicate rows correctly you can switch to delete instead of select *.

Upvotes: 1

Related Questions