Updating Contact Records Based on Related Opportunities

A common scenario that leads to the “Too Many SOQL Queries: 101” error in Salesforce is when SOQL queries are placed inside loops. This can quickly exhaust the governor limit of 100 SOQL queries per transaction.

Suppose you have a requirement to update all Contacts associated with an Account to “High Value” if any related Opportunity is worth more than $1,000,000.

Code That Causes the Error:

List<Contact> contactsToUpdate = new List<Contact>();

for (Contact c : [SELECT Id, AccountId, Status FROM Contact WHERE AccountId IN (SELECT AccountId FROM Opportunity WHERE Amount > 1000000)]) {
    c.Status = 'High Value';
    contactsToUpdate.add(c);
}

update contactsToUpdate;

In this example, the SOQL query is executed inside a loop. If there are more than 100 Contacts associated with qualifying Opportunities, this code will result in more than 100 SOQL queries, causing the “Too Many SOQL Queries: 101” error.

Solution: Move the Query Outside the Loop

To avoid this error, you should move the SOQL query outside the loop and use a collection to process the records:

Map<Id, Account> accountsWithHighValueOpps = new Map<Id, Account>([SELECT Id FROM Account WHERE Id IN (SELECT AccountId FROM Opportunity WHERE Amount > 1000000)]);

List<Contact> contactsToUpdate = new List<Contact>();
for (Contact c : [SELECT Id, AccountId, Status FROM Contact WHERE AccountId IN :accountsWithHighValueOpps.keySet()]) {
    c.Status = 'High Value';
    contactsToUpdate.add(c);
}

if (!contactsToUpdate.isEmpty()) {
    update contactsToUpdate;
}

In this revised solution, the SOQL query is executed only once, and the results are stored in a map. The loop then iterates over the Contacts, updating their status based on the pre-queried Account information. This approach ensures that the number of SOQL queries does not exceed the governor limit.