I want to mark checkbox(true) for duplicate__c field whose default value is false in Account. Unfortunately my debug is working fine but i cant update duplicate__c field as true

My querry is

//Query for Duplicate Account Name, Prospect Code who dont have Relationship with Contact and Opportunity

List<AggregateResult> acc=[SELECT Name  , Prospect_Code__c  , COUNT(Id) ids FROM Account where Id Not In (Select AccountId from Contact) And Id Not In (Select AccountId from Opportunity) GROUP BY Name, Prospect_Code__c  HAVING count(Id)>1];

Map<String,boolean> act = new Map<String,boolean>();

for(AggregateResult aggres : acc)



    System.debug('Finding duplicate AccName,ProspCode with no relation with Contact and Opp'+aggres);


You aren't performing any DML here - and you can't, because your aggregate query doesn't return any Ids as you've aggregated them:

SELECT Name, Prospect_Code__c  , COUNT(Id) ids ...

You'll have to perform a second query based on the Name and Prospect_Code__c of each AggregateResult to find the Accounts you're considering duplicated and update them. Your Map<String, Boolean> is not really going to help here.

You can iterate over the AggregateResult list to perform one query each, which puts you in limits trouble. There's a better but much more complex approach I'll sketch here, but you'll have to write a fair amount of code to implement it.

First, accumulate a set of composite keys: the combinations of Name and Prospect Code that are duplicated. I'd do this with a Set<String> compositeKeySet, where the values are something like Prospect_Code__c + '\n' + Name. Then, accumulate sets of Name and Prospect_Code__c values and perform a single, bulkified, but overly broad query:

SELECT Name, Prospect_Code__c FROM Account WHERE Name IN :nameSet AND Prospect_Code__c IN :prospectCodeSet

Then, iterate over the results of that query. For each Account in the results, check if its composite key (Prospect_Code__c + '\n' + Name) is in compositeKeySet. If it is, you know its specific combination of Name and Prospect Code was one of those identified as a duplicate. You can then update that Account.

It's important to bear in mind that this is a very expensive operation in terms of the queries you're going to run and how much system resources they require. It is likely you will have to implement this as an Apex batch class, or even two Apex batch classes - one to run the first query, chaining into the second to actually process the results. How critical the architecture ends up being is heavily dependent on the data volume in the target org and the extent of the duplication.

If you don't have experience tuning query performance and implementing this kind of architecture, I'd encourage taking advantage of this opportunity to work with a senior developer on your team or in your local user group to work out the complex details.

  • I didnt get as i am new to Salesforce also if possible can you send the code based on my requirement above //Query for Duplicate Account Name, Prospect Code who dont have Relationship with Contact and Opportunity – Palash Jun 19 at 12:41
  • @Palash No, I can't write the code for you. This requirement is an hour or three of work for an experienced developer. If you attempt to do the implementation and get stuck on a specific problem, you're welcome to ask new questions, but we don't provide code at SFSE. – David Reed Jun 19 at 12:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.