Oh my god. It's full of code!

Deleting Duplicate Records But Leave One – Salesforce SOQL

So this is a pretty common thing in regular SQL. You have a bunch of duplicates, in this scenario case objects (we have an automatic case creation process that recently went haywire and created a bunch of cases that where all the the same). So of course we wanted to delete the duplicates, yet leave one of the original records (even though there were dupes, it was still a valid case). So in a panic I threw together this quick method for removing duplicate cases by their subject and leave one of each dupe. This was written pretty hastily, but it should work in most instances. Just run this anonymously using your favorite IDE or I think you can do it in the system log as well.

//First we need to find any cases that have duplicate records. This whole deal uses the subject field to
//identify dupes. So if you need to modify this for another object, find a field you can use identify dupes and
//replace anything that says 'subject' with that field.
list<AggregateResult> badCases = [select count(id)numCases, subject from case where createdDate = LAST_90_DAYS and isClosed = false and subject !='' group by subject  having count(id) > 1];
list<string> badCaseNames = new list<string>();

//loop over every row we found, and add the subject to a list so we can find them again in another query.
for(AggregateResult ar : badCases)
{
        badCaseNames.add(string.valueOf(ar.get('subject')));
}
//Now run a query that gets the ID's of the offending cases. The order by line at the end is CRUCIAL. You MUST
//order the records by the field you are using to identify them as dupes.
list<case> listCases = [select id, subject from case where subject in :badCaseNames order by subject];
//Since we don't want to delete ALL the cases we just found (we have to leave one behind) we create another list
//of cases that will actually get deleted.
list<case> casesToDelete = new list<case>();

//A variable to hold the subject of the last case looked at. If it matches, then it's a dupe.
//hence the reason we needed to sort by subject
string lastCaseName = 'placeholder12345';
for(case c : listCases)
{
    //here is that logic I was talking about. If the current case name
    //is the same as the one we just looked at, then obviously it's a dupe
    //and should get deleted. Otherwise don't do anything cause it's the one case
    //we want to leave behind.
    if(c.subject == lastCaseName)
    {
        casesToDelete.add(c);
    }
    //set the variable for the next iteration
    lastCaseName = c.subject;
}
//If there is anything to delete
if(!casesToDelete.isEmpty())
{
    //delete those suckers.
    delete casesToDelete;
}

2 responses

  1. person

    This is so clever.

    October 27, 2012 at 6:41 am

    • Thanks! I thought it was kinda cool 🙂

      October 31, 2012 at 9:16 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s