Apex Code Development You need to sign in to do that
Don't have an account?
hometeam Too many query rows - but needing to update all of them
I know this is one of the most popular topics on the board and I've spent a day reading and trying several of the posted suggestions but I'm not finding a workable solution (or one that I understand) to help me.
What I need to do is traverse through all open opportunities and based on some criteria on the account either mark the opportunity as closed/won or closed/lost. So I will have a large query. I could in theory split the query but then I'm doing multiple loops and from what I read that's not best practises.
This is my class and what I'm needing to understand is what is the correct way I handle the large number of rows returned from the query when I have to update each row.
public class clsCloseOppty {
public void CloseOppty() {
List<Opportunity> OpptyToUpdate = new List<Opportunity>();
// Set This Years Close date to use in the record query
Date dCloseDate = date.newinstance(date.today().year(),04,01);
// Query for this years Opportunities along with the Account Type
List<Account> ClsOpptys = [Select a.name, a.Type, (Select o.Id, o.Name from Opportunities o where o.closedate = :dCloseDate) From Account a];
// Loop through ClsAccts
for (Account a : ClsOpptys) {
// Set value for Stage
string strStage = 'Closed/Lost';
if (a.Type == 'Existing Agency') {
strStage = 'Closed/Won';
}
// Get the oppty Id
for (Opportunity opp : a.opportunities){
ID id = opp.Id;
Opportunity O = New Opportunity(Id=Id, StageName=strStage);
OpptyToUpdate.add(O);
}
}
update OpptyToUpdate;
}
}
This is my query:
There's a couple of ways to handle this, depending on the volumes.
If you are guaranteed to have less than 10000 records, you can use the multiple record version of the SOQL for loop. In this, each time through the for loop you process 200 records.
for (Account[] ClsOpptys : [Select a.name, a.Type, (Select o.Id, o.Name from Opportunities o where o.closedate = :dCloseDate) From Account a]) { // Loop through ClsAccts for (Account a : ClsOpptys) {However, this won't work if you can have more than 10000 records, as thats a limit for the maximum number that can be retrieved.
The other way (and more suitable IMHO) is to use batch apex. This allows you to iterate all records in the system in batches. There's full documentation at:
http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm
If you need to run this at regular intervals, I'd suggest you look at scheduled apex too.
All Answers
There's a couple of ways to handle this, depending on the volumes.
If you are guaranteed to have less than 10000 records, you can use the multiple record version of the SOQL for loop. In this, each time through the for loop you process 200 records.
for (Account[] ClsOpptys : [Select a.name, a.Type, (Select o.Id, o.Name from Opportunities o where o.closedate = :dCloseDate) From Account a]) { // Loop through ClsAccts for (Account a : ClsOpptys) {However, this won't work if you can have more than 10000 records, as thats a limit for the maximum number that can be retrieved.
The other way (and more suitable IMHO) is to use batch apex. This allows you to iterate all records in the system in batches. There's full documentation at:
http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_batch_interface.htm
If you need to run this at regular intervals, I'd suggest you look at scheduled apex too.
You're fantastic! Thank you. This works perfectly