SOQL Limit hit inside FOR Loop

Question

I apologise in advance as i know this should be simple and other people ask this same question but no matter how i try to re-write my code i keep getting errors so ive given in and thought id ask for some help.

For each price book entry against the standard price book, i need to find matching non-standard price book entrys that have the same Product2Id and update them. My code is below and obviously im hitting the SOQL query limit since its inside the for loop.

    pricebook2 SPB = [SELECT Id FROM Pricebook2 WHERE IsActive = true AND IsStandard = true];   
    String SPBId = SPB.Id;  

 //get book entries for the standard price book
    List<pricebookEntry> StandardPBEntries = [SELECT Id,Product2Id FROM pricebookEntry WHERE priceBook2Id = :SPBId];  

 //loop around standard price book entries looking for matches against non standard entries
    for (pricebookEntry StandardEntry : StandardPBEntries) 
           {              
            String prod2Id  = StandardEntry.Product2Id;

             //look for matches
             List<pricebookEntry> Matches = [SELECT Id, Name, Pricebook2Id, Product2Id, IsActive, Is_Negotiated__c FROM PricebookEntry 
                                            where product2id = :prod2Id and isactive = true 
                                            and priceBook2Id IN (SELECT Id FROM Pricebook2 WHERE IsActive = true AND IsStandard = false)];

                 if(Matches.size() > 0)                    
                 {
                    for (pricebookEntry aMatch : Matches)
                    {
                        //update required data
                    }                           
                 }       

            }

Answer 1

Do it like this:

pricebook2 SPB = [SELECT Id FROM Pricebook2 WHERE IsActive = true AND IsStandard = true];   
String SPBId = SPB.Id;  

//get book entries for the standard price book
List<pricebookEntry> StandardPBEntries = [SELECT Id,Product2Id FROM pricebookEntry WHERE priceBook2Id = :SPBId];  

 //loop around standard price book entries looking for matches against non standard entries
Set<Id> product2IdsSet = new Set<Id>();
for(pricebookEntry StandardEntry : StandardPBEntries){            
    product2IdsSet.add(StandardEntry.Product2Id);                
}


//look for matches
List<pricebookEntry> Matches = [SELECT Id, Name, Pricebook2Id, Product2Id, IsActive, Is_Negotiated__c FROM PricebookEntry 
                                where product2id IN: product2IdsSet and isactive = true 
                                AND priceBook2Id IN (SELECT Id FROM Pricebook2 WHERE IsActive = true AND IsStandard = false)];

for(pricebookEntry aMatch : Matches){
    //update required data
}        

There is no need to add the size() check, foreach will handle this even if there is no record returned by the query.