4

What kind of approach should I use in deleting all the duplicate records, but retain at least one?

As in the system, there are duplicate records which are added before, I want to remove all the records but retain for one unique for Student (example).

Here is sample records
Id, Class Name, StudentId
xxx1,Science Class, STUD_001
xxx2,Science Class, STUD_001
xxx3,Science Class, STUD_001
xxx4,Science Class, STUD_005

Above example: for STUD_0001 , only one record should be retained and other two duplicate should be removed.

I am using AggregateResult to get the duplicate counts for each student. After that, I put StudentId into Set. After that, I don't know the better way to remove those duplicate records.

How can I take this approach ?

7 Answers 7

2

How about a Batch job ? You need to select ALL the record of the target Object

Your query should be something like this "SELECT Id, StudentId__c, Class_Name_c From Student_c"

Then use Database.Stateful (http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm) to maintain state between your Batches and create an instance variable Set sUniqueKey

For each record in the Batch and your Set contains the UniqueKey (a concatenated string) delete the record. If not add the UniqueKey and continue to the next record allowing the new Key to survive.

When done you will have only 1 record per key.

The question is do you need this only once or on a regular basis ? If only once, dataloader and Excel could also

4
  • thats a great idea! Database.stateful !! I wouldn't have thought about that!
    – Kevin P
    Sep 23, 2013 at 16:44
  • I will accept this as an answer. Although I won't use Batch job, I will use the idea of using MAP for checking unique Id and use that map to make remove list. I decided not to use batch, as the duplicate records only happen at sometimes. And only three to four duplicate records per student ID. Thanks for the input everyone. I have learned a lot from this salesforce.stackexchange.com site.
    – kitokid
    Sep 24, 2013 at 3:32
  • If you need something to prevent users from creating duplicates, then you can create a new field and set this as externalId unique. Then use a workflow to populate the value concatenating the values of the unique key and you're done.
    – ManSpan
    Sep 24, 2013 at 8:32
  • Thanks for the further input. I will approach you if I have more question on this matter. Super thanks.
    – kitokid
    Sep 24, 2013 at 10:14
0

That's going to be very hard to do in Apex since you don't have access to full SQL. I personally would export the data to a local database server and use it to generate a list of duplicate Salesforce ids to delete. I would then feed those ids into the data tool of your choice to bulk delete them.

Caveat: Always test mass-delete operations in a sandbox first!

0

I really like @manos' answer above, but it's kind of the nuclear option.

There are dozens of de-duplication managed packages on the App exchange all of which could probably work. I think DemandTools is kind of the Cadillac of de-duplication but there's dupe blocker and perhaps more importantly, Salesforce Labs has Dupe Eliminator: https://appexchange.salesforce.com/listingDetail?listingId=a0N3000000178g7EAA which if nothing else is open source and will give you a great idea about how to go about doing de-duplication at scale. (By reading the source) as well as a starting point... (plus, I like to pimp SalesforceLabs projects, they never get the streed cred they deserve.)

0

To do this on an ongoing basis, you can use an apex Trigger. Just do a query on any existing records that match that field, and delete the new record. Here I'm interating on the queried list, but you could easily put that list in a map<String, Student__c> for slightly more efficient code.

Trigger dedupeTrigger on Student__c (before insert)
{
    set<String> uniqueIds = new set<Id>();

    for (Student__c s : Trigger.new)
        uniqueIds.add(s.StudentId__c);

    list<Student__c> existingstudents = [select StudentId__c from Student__c where StudentId__c in :uniqueIds];

    list<Student__c> dupestodelete = new list<Student__c>();
    for (Student__c s : Trigger.new)
    {
        for (Student__c existing : existingstudents)
        {
            if (s.StudentId__c == existing.StudentId__c)
            {
                dupestodelete.add(s);
                continue;
            }
        }
    }

    delete dupestodelete;

}

If you just need this done once or periodically, the batch job as suggested by Manos would work also.

2
  • imm.. Actually that kind of trigger has provided in that custom object. As trigger is mananged, I can't see the code. And that is provided from SF according to my management. But the problem is sometimes, duplicate records are inserted. When multiple users do the same actions in inserting the records, it somehow bypass the trigger(I believe). Because when I try to insert two duplicate records in my Test class, I can't due to Custom Field Validation exception. So I need to remove the duplicate records as to prevent the future duplicate records.
    – kitokid
    Sep 24, 2013 at 1:36
  • So it sounds like you just need a one-time update, and your system will run fine from then on. Sep 24, 2013 at 16:11
0

I took @SDFC 44 's answer and made some fixes and adjustments (in my issue I needed to deduplicate cases made past a particular created date, and only remove when status was New)

List<AggregateResult>  results = [select message_id__c, count(id) , min(createddate) 
from case c
where createddate > 2022-09-08T01:01:01.000Z
and message_id__c != null
group by message_id__c
having count(id) > 1 
order by count(id) desc];

system.debug(results);

map<String, string> first_message_ids = new map<String, string>();
for (AggregateResult ag : results){
first_message_ids.put( (string) ag.get('message_id__c'), string.valueof(ag.get('expr1')) );
}


system.debug(first_message_ids);


map<string, id>  deleter = new map<string, id>();
list<case> listtodelete = new list<case>();
for (case c : [select id, message_id__c from case where message_id__c in :first_message_ids.values() and status = 'New']){
    if (!deleter.containsKey(c.message_id__c)){
        deleter.put(c.message_id__c, c.Id); 
    } else {
        listtodelete.add(c);
    }
}
        
system.debug(listtodelete);
delete listtodelete;
2
  • I believe it should have been message_id__c in :first_message_ids.keyset() here.
    – dzh
    May 17, 2023 at 6:47
  • You'd also need to sort this query by date.
    – dzh
    May 17, 2023 at 6:47
0

Please find below code for deleting duplicates from records :

Map<String, String> Ids = new Map<String, String>();
for (AggregateResult [] acc : [select count(id), name from account group by name having count(id) > 1]) {
    for (AggregateResult accn : acc) {
        Ids.put(String.valueOf(accn.get('expr0')), String.valueOf(accn.get('name')));
    }
}
Map<String, Id> mapaccIdname = new Map<String, Id> ();
List<Account> listtodelete = new List<Account>();
for (Account acc : [SELECT Id, name FROM Account WHERE Name in :Ids.values()]) {
    if (!mapaccIdname.containskey(acc.name)) {
        mapaccIdname.put(acc.name, acc.Id);
    } else {
        listtodelete.add(acc);
    }
}

delete listtodelete;
1
  • To those trying this out it has a few mistakes see my answer for the fixes
    – Jay Rizzi
    Sep 27, 2022 at 20:48
0

Here's a script with a unit test that uses same techniques others mentioned.

It deletes duplicate Contacts by Email keeping the newest one (by CreatedDate)

Test:

@IsTest class DupeDeleterTest {
    @IsTest static void test1(){
        Contact a = new Contact(
                LastName = 'a',
                Email = '[email protected]'
        );
        Contact b = new Contact(
                LastName = 'b',
                Email = '[email protected]'
        );
        Contact c = new Contact(
                LastName = 'c',
                Email = '[email protected]'
        );
        Contact d = new Contact(
                LastName = 'd',
                Email = '[email protected]'
        );
        Contact e = new Contact(
                LastName = 'e',
                Email = '[email protected]'
        );
        insert a;
        insert b;
        insert c;
        insert d;
        insert e;

        Test.setCreatedDate(e.Id, Datetime.now().addDays(-1));
        Test.setCreatedDate(c.Id, Datetime.now().addDays(-1));
        Test.startTest();
        DupeDeleter.deleteDupes();
        Test.stopTest();
        System.assert([SELECT count() FROM Contact WHERE id =:a.Id] == 1);
        System.assert([SELECT count() FROM Contact WHERE id =:b.Id] == 0);
        System.assert([SELECT count() FROM Contact WHERE id =:c.Id] == 0);
        System.assert([SELECT count() FROM Contact WHERE id =:d.Id] == 1);
        System.assert([SELECT count() FROM Contact WHERE id =:e.Id] == 0);
    }
}

Script:

public with sharing class DupeDeleter {
    public static void deleteDupes(){
        List<AggregateResult> results = [
                SELECT
                        Email,
                        COUNT(Id)
                FROM
                        Contact
                WHERE
                        Email != NULL
                GROUP BY Email
                HAVING COUNT(Id) > 1
                ORDER BY COUNT(Id) DESC
        ];
        System.debug(results);
        Set<String> emails = new Set<String>();
        for (AggregateResult ag : results) {
            emails.add((String) ag.get('Email'));
        }
        Set<String> firsts = new Set<String>();
        List<Contact> toDelete = new List<Contact>();
        Contact[] contacts = [SELECT Id, Email FROM Contact WHERE Email IN :emails ORDER BY Email, CreatedDate DESC];
        System.debug(contacts);
        for (Contact i : contacts) {
            if (!firsts.contains(i.Email)) { // retain first
                firsts.add(i.Email);
            } else { // delete rest
                toDelete.add(i);
            }
        }
        System.debug(toDelete.size());
        delete toDelete;
    }
}

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .