function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
RarLopzRarLopz 

Too many Query Rows - Developer Console

Can someone please assist, how do i get around this exception of 'Too many query Rows 5001'

 
list<Id> accId = new list<Id>();

list<Contact> conDSA = [Select Id, Name, DSA__c, AccountID from Contact 
                        Where DSA__c = true AND AccountID!=null ];

system.debug('Number of Contacts that are DSA ' +conDSA.size());
for (Contact c: conDSA){
system.debug('>>> ' +c.Name + c.AccountId);
    accId.add(c.AccountId);

}

list<AccountContactRelation> acr = [SELECT Id, isDSA__c,AccountId, ContactId 
                                    FROM AccountContactRelation 
                                    Where AccountId IN :accId ];

system.debug('Number of records in ACR are: ' +acr.size());

for (AccountContactRelation a: acr){
    	a.isDSA__c = true;
}
update acr;

 
Best Answer chosen by RarLopz
Raj VakatiRaj Vakati
Looks like you are calling this code from the developer console so you are getting the  all the record so can you try like this

 
list<Id> accId = new list<Id>();

list<Contact> conDSA = [Select Id, Name, DSA__c, AccountID from Contact 
                        Where DSA__c = true AND AccountID!=null Limit 500];

system.debug('Number of Contacts that are DSA ' +conDSA.size());
for (Contact c: conDSA){
system.debug('>>> ' +c.Name + c.AccountId);
    accId.add(c.AccountId);

}

list<AccountContactRelation> acr = [SELECT Id, isDSA__c,AccountId, ContactId 
                                    FROM AccountContactRelation 
                                    Where AccountId IN :accId Limit 9999];

system.debug('Number of records in ACR are: ' +acr.size());

for (AccountContactRelation a: acr){
    	a.isDSA__c = true;
}
update acr;

https://help.salesforce.com/articleView?id=000181404&language=en_US&type=1

All Answers

Tyler Tran 94Tyler Tran 94
Hi RarLopz!
Please check your debug log file of this class, or you can post it here! Because my problem is same as your, hope I can help!
NagendraNagendra (Salesforce Developers) 
Hi Rarlopz,

Sorry for this issue you are facing.

The exception is being thrown because the maximum number of rows you can return in SOQL calls in Apex is 50000.
 
The two SOQL calls you have above are not filtering, I.E., they do not have a LIMIT clause which could prevent this exception.
 
In your VF Page, you could try to limit this to the number of records.
 
It looks like your org has an extensive amount of data, so think about the use case. Even if you could display 50,000 products on a page, who is going to be able to read that?
 
If you add Where and Limit clauses to your SOQL, you'll be able to resolve the error.
 
Hope this helps.

KIndly mark this as solved if the reply was helpful.

Thanks,
Nagendra
Raj VakatiRaj Vakati
Looks like you are calling this code from the developer console so you are getting the  all the record so can you try like this

 
list<Id> accId = new list<Id>();

list<Contact> conDSA = [Select Id, Name, DSA__c, AccountID from Contact 
                        Where DSA__c = true AND AccountID!=null Limit 500];

system.debug('Number of Contacts that are DSA ' +conDSA.size());
for (Contact c: conDSA){
system.debug('>>> ' +c.Name + c.AccountId);
    accId.add(c.AccountId);

}

list<AccountContactRelation> acr = [SELECT Id, isDSA__c,AccountId, ContactId 
                                    FROM AccountContactRelation 
                                    Where AccountId IN :accId Limit 9999];

system.debug('Number of records in ACR are: ' +acr.size());

for (AccountContactRelation a: acr){
    	a.isDSA__c = true;
}
update acr;

https://help.salesforce.com/articleView?id=000181404&language=en_US&type=1
This was selected as the best answer
RarLopzRarLopz
Thanks @rajvakati , Nagendra and Tai Tran 

I don'nt want to set limit to the query because i will then have to run this query 16 times to update all the 80k records

I  plan to put my logic in a batch class and then execute the batch from dev console. 

This is what i did . 
 
global class IsDSAOnAccountContactRelation implements Database.Batchable<sObject> {
    global Database.QueryLocator start(Database.BatchableContext BC) {
            // collect the batches of records or objects to be passed to execute
             
            String query = ('Select Id, Name, DSA__c, AccountID FROM Contact WHERE DSA__c = true AND AccountID!=Null AND EMAIL!=Null');
                            
            return Database.getQueryLocator(query);
    }
     
    global void execute(Database.BatchableContext BC, List<Contact> conDSA) {
        
        // process each batch of records
 		list<Id> accId = new list<Id>();
         
        for (Contact c: conDSA){
			system.debug('>>> ' +c.Name + c.AccountId);
	    	accId.add(c.AccountId);

		}
		
		list<AccountContactRelation> acr = [SELECT Id, isDSA__c,AccountId, ContactId 
                                    FROM AccountContactRelation 
                                    Where AccountId IN :accId ];

		system.debug('Number of records in ACR are: ' +acr.size());

		for (AccountContactRelation a: acr){
		    	a.isDSA__c = true;
		}
        try {
            // Update the AccountContactRelation Record
            update acr;
         
        } catch(Exception e) {
            System.debug(e);
        }
         
    }   
     
    global void finish(Database.BatchableContext BC) {
        // execute any post-processing operations
  	}
}

And then in Dev Console I will do this - 


IsDSAOnAccountContactRelation  batchclass = new IsDSAOnAccountContactRelation ();
Database.executebatch(batchclass );

 
Raj VakatiRaj Vakati
Use the below code and i guess you need toadd false in where condition 

Select Id, Name, DSA__c, AccountID FROM Contact WHERE DSA__c = false AND AccountID!=Null AND EMAIL!=Null

 
global class IsDSAOnAccountContactRelation implements Database.Batchable<sObject> {
    global Database.QueryLocator start(Database.BatchableContext BC) {
            return Database.getQueryLocator('Select Id, Name, DSA__c, AccountID FROM Contact WHERE DSA__c = true AND AccountID!=Null AND EMAIL!=Null');
    }
     
    global void execute(Database.BatchableContext BC, List<Contact> conDSA) {
 		list<Id> accId = new list<Id>();
        for (Contact c: conDSA){
	    	accId.add(c.AccountId);
		}
		list<AccountContactRelation> acr = [SELECT Id, isDSA__c,AccountId, ContactId 
                                    FROM AccountContactRelation 
                                    Where AccountId IN :accId ];
		for (AccountContactRelation a: acr){
		    	a.isDSA__c = true;
		}
        try {
            update acr;
        } catch(Exception e) {
            System.debug(e);
        }
    }   
    global void finish(Database.BatchableContext BC) {
  	}
}

 

 
mukesh guptamukesh gupta
Hi RarLopz,

you can follow below code steps :-
 
/* BatchRecordUpdate will update any 1 field on any 1 object.
 * 
 * String values must be set to Object, Field and Value to be added to Field.
 * 
 * Query can be modified to limit records updated.
 * Example below excludes records for which Degree Offering already equals the new value.
 * 
 * All 4 strings' values must be set to operate when executed.
 * 
 * Execute in Anonymous Apex.
 * 
 * By Default it will run 200 records at at a time.
 * CODE TO RUN IN ANONYMOUS APEX
	String e = 'Lead'; // Object to be updated
	String f = 'LeadSource'; // field to be updated.
	String v = 'DarkWeb'; // value with which field will be populated.
	String q = 'SELECT ' + f + ' FROM ' + e; // Query to which more filters can be added like: + 'AND IsConverted = false';
	Id batchInstanceId1 = Database.executeBatch(new BatchFieldUpdater(q,e,f,v));
 *
 *  Different Batch Sizes can be set by modifying the final line to add batch size after the list of arguments.
 * Example: Id batchInstanceId1 = Database.executeBatch(new BatchRecordUpdater(q,e,f,v),1);
 */

global class BatchFieldUpdater  implements Database.Batchable<sObject>{
   global final String Query;
   global final String Entity;
   global final String Field;
   global final String Value;

   global BatchFieldUpdater(String q, String e, String f, String v){
             Query=q; Entity=e; Field=f;Value=v;
   }

   global Database.QueryLocator start(Database.BatchableContext BC){
      return Database.getQueryLocator(query);
   }

   global void execute(Database.BatchableContext BC, 
                       List<sObject> scope){
      for(Sobject s : scope){s.put(Field,Value); 
      }      update scope;
   }

   global void finish(Database.BatchableContext BC){

   }

}

if you found this useful then, PLEASE MARK AS A BEST ANSWER!!


Regards
Mukesh
RarLopzRarLopz
@Mukesh Thanks ! This is a nice utility batch class. 
Can you please  tell me -  since this is generic class, how would i write a test class for this class. ?

@RajVakati, Thanks for your response ' i guess you need toadd false in where condition 
Select Id, Name, DSA__c, AccountID FROM Contact WHERE DSA__c = false AND AccountID!=Null AND EMAIL!=Null'

Basically I am copying the value of DSA__c from Contact to isDSA__c in AccountContactRelation, thats why my filter is DSA__c = true , and mI am assigning  isDSA__ = true.  





 
Ajay K DubediAjay K Dubedi
Hi RarLopz, 

The exception is being thrown because the maximum number of rows you can return in SOQL calls in Apex is 50000.
The two SOQL calls you have below are not filtering . not have a LIMIT clause which could prevent this exception.
If you add Limit clauses to your SOQL, you'll be able to resolve the error.
list<Id> accId = new list<Id>();

list<Contact> conDSA = [Select Id, Name, DSA__c, AccountID from Contact 
                        Where DSA__c = true AND AccountID!=null Limit 10000];

system.debug('Number of Contacts that are DSA ' +conDSA.size());
for (Contact c: conDSA){
system.debug('>>> ' +c.Name + c.AccountId);
    accId.add(c.AccountId);

}

list<AccountContactRelation> acr = [SELECT Id, isDSA__c,AccountId, ContactId 
                                    FROM AccountContactRelation 
                                    Where AccountId IN :accId limit 10000 ];

system.debug('Number of records in ACR are: ' +acr.size());

for (AccountContactRelation a: acr){
        a.isDSA__c = true;
}
update acr;

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.
Thanks,
Ajay Dubedi
RarLopzRarLopz
Thanks Ajay Dubedi.