CODEWITHSUNDEEP
postgresql
akhilesh kedarisetty
akhilesh kedarisetty

Reputation: 117

Deleting records using select statement

I am have to delete records whose count is > 1. For this, at the first step, I need to pick rec_id from custd table whose count is greater than 1 and delete data for that particular rec_id except the rec_id having highest id value.

select rec_id , field_id, count(*) 
from mst.custom_data cd 
group by rec_id, field_id 
having count(*) > 1;

The output looks like :

rec_id   field_id     count
141761;     3;          2
117460;     7;          2
141970;     2;          2

select * from mst.custom_data   where rec_id = '141761' and field_id=3

id        field_id   rec_id
200;        3;       141761
53791;      3;       141761

So, the above which is containing the least id should be deleted.

Upvotes: 1

Views: 66

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

We can try using a correlated subquery here:

DELETE
FROM mst.custom_data m1
WHERE EXISTS (SELECT 1 FROM mst.custom_data m2
              WHERE m1.rec_id = m2.rec_id AND m1.field_id = m2.field_id
              GROUP BY rec_id, field_id
              HAVING COUNT(*) > 1 AND MAX(m2.id) > m1.id);

The correlated subquery returns a record for a given (rec_id, field_id) group value if the outer id value being considered for deletion is stictly less than the max id for that group. This is the logic you requested.

Upvotes: 1

Related Questions