Deleting records using select statement

Question

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.

Tim Biegeleisen · Accepted Answer · 2019-09-13 06:15:28Z

1

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.

edited Sep 13, 2019 at 6:15

answered Sep 13, 2019 at 6:04

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

akhilesh kedarisetty Over a year ago

What is the use of 1 in the subquery ? what does it pick?

Tim Biegeleisen Over a year ago

@akhileshkedarisetty I added an explanation. Also please let me know if the query worked for you.

akhilesh kedarisetty Over a year ago

Yeah that's working perfectly fine....!!!! But i am having little confusion with the query can you please say me that what will be the output of inner query when you used select 1 and in the delete statement we even have not mentioned where id in(subquery) .......So how will it know to delete only those particular records? What will delete statement search to delete from subquery?

Tim Biegeleisen Over a year ago

I can't really explain more than I have without giving you a tutorial on the basics of SQL (which would be out of scope for a single SO question).

Collectives™ on Stack Overflow

Deleting records using select statement

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related