0

I don't need the exact query , just the logic of how I should go about it:
I need to compare for equality all records of a table for a given set of fields. If count of such similar records exceeds a particular value, I have to delete all those records.

For ex. A table contains columns A, B, C, D, E, F. I want to delete all such records whose column A, C, D are same and if count of such records exceeds (say) 100.

2
Contributors
3
Replies
4
Views
5 Years
Discussion Span
Last Post by DJSAN10
0

Just found out that comparison can be done for same table using something like this :
select * from table as t1,table as t2 where t1.column1 = t2.column1

Is this the correct way of doing it?? Can it be further optimized??

0

that would come at a later stage.
first you have to figure out the number of records:

select A,C,D, count(*) from table
group by A,C,D

count will be the number of records for each unique combination of A,C,D.
If you want to delete all records with count > 100 then you would:

delete from table inner join 
(select A,C,D, count(*) as 'counter' from table
group by A,C,D
having count(*) > 100 ) a 
on table.A = a.A
and table.C = a.C
and table.D = a.D

BUT, be warned that this won't delete only the "excess" records (if counter = 120 it won't just delete the 20 records, but the whole 120).
If you want to delete only the 20 you have to:

  delete from table inner join 
    (select A,C,D, ROW_NUMBER() OVER (ORDER BY A ) as 'Row'
    from 
    (select A,C,D, count(*) as 'counter' from table
    group by A,C,D
    having count(*) > 100 ) a ) b 
    on table.A = b.A
    and table.C = b.C
    and table.D = b.D
    and Row > 100 

Please note that I haven't tested any of these and they may contain typos or other errors.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.