what is the best way to update hundreds of existing records

Question

I need to update existing records. when i am trying to update single record in developer console(anonymous window) by passing ID in where clause. But, when i try to update multiple records i'am facing error. can any one please tell me is way of updating correct or not.

This is the query for update single record:

Accreditation__c accr = [SELECT Id, Account__c, Business_Plan__c 
                           FROM Accreditation__c WHERE id = 'a5PU0000000PCVX'];

accr.Business_Plan__c = [select Name from Business_Plan__c 
                          where Partner_Account__c = :accr.Account__c 
                            and Year__c = '2016'].id;

update accr;

This is the query for update multiple records:

    Accreditation__c[] accr = [SELECT Id,Business_Plan__c,Account__c FROM Accreditation__c WHERE isActive__c = true AND is_branch__c = true limit 2];

system.debug('accr size = '+accr.size());

if(!accr.isEmpty()){

for(Accreditation__c accrdt : accr)
   accrdt.Business_Plan__c = [select Name from Business_Plan__c where Year__c = '2016' AND Partner_Account__c = :accrdt.Account__c];

 update accr;
 }

The error is while updating multiple records:

Illegal assignment from List to Id

Can anybody please suggest me to avoid this error or what is fastest way to update existing records.

Answer 1

The error message means your query is not returning any results. I see that you're trying to query accounts that are from the same list your writing to, not the list you originally queried (accr.Account__c vs accrdt.Account__c). I suggest you rewrite it to look more like the following, esp since you're trying to return multiple records.

Accreditation__c[] accr = [SELECT Id,Business_Plan__c,Account__c FROM Accreditation__c WHERE isActive__c = true AND is_branch__c = true];
system.debug('accr size = '+accr.size());
if(!accr.isEmpty()){
   for(Accreditation__c accrdt : accr)
   accrdt.Business_Plan__c = [select Name from Business_Plan__c where  Partner_Account__c.Year__c = '2016' AND Partner_Account__c.Year__c IN :accr.Account__c];

   update accr;
 }

What's not clear is whether you initial query isn't returning any results or whether your 2nd query isn't returning any results. The debug and if statement should tell you that.

I don't know if adding that idat the end of your 2nd query will work for the for loop or if it's even needed. That part isn't clear to me. It's my sense that it's not, thus the reason I've omitted it and rewritten your query using the different structure in addition to using the correct variable. You can of course try it both ways since I don't know your schema. Clearly though the variable name is an issue.

Edit

I was so focused on the one error that I overlooked another obvious one.

accrdt.Business_Plan__c has to be of object type Business_Plan__c to which you can equate the query since it's not a field at that point, but instead an object.

Your code should look like this:

Accreditation__c[] accr = [SELECT Id,Business_Plan__c,Account__c FROM Accreditation__c WHERE isActive__c = true AND is_branch__c = true];
system.debug('accr size = '+accr.size());

if(!accr.isEmpty()){
   for(Accreditation__c accrdt : accr)
   Business_Plan__c bpc = [select Name from Business_Plan__c where  Partner_Account__c.Year__c = '2016' AND Partner_Account__c.Year__c IN :accr.Account__c];

   update bpc;
 }

Answer 2

Why are you needing to limit your update call to 2 records and how do you know which 2 records the query will return?

Assuming you need to update more than 2 records and you were just using 2 as an example AND you don't have multiple Business Plan Records in 2016 with the Same Partner Account value, I would format your code like this:

List<Accreditation__c> accList = new List<Accretitation__c>();

for(Accreditation__c accrdt : [SELECT Id,Business_Plan__c,Account__c FROM Accreditation__c WHERE isActive__c = true AND is_branch__c = true){
    for(Business_Plan__c bp : [select Id, Partner_Account__c from Business_Plan__c where Year__c = '2016']){
        if(accrdt.Account__c == bp.Partner_Account__c){
            accrdt.Business_Plan__c = bp.id;
            acctList.add(accrdt);
        }
    }
 }
if(acctList.size() > 0){
update acctList;
}

Answer 3

My first answer is to use Data Loader/Excel or its ilk; but if you want to go the execute anonymous route:

// select all Accreditations that need to be updated
Accreditation__c[] accrList = [SELECT Id,Business_Plan__c,Account__c 
                            FROM Accreditation__c 
                            WHERE isActive__c = true AND is_branch__c = true];

// Find the Business Plans for 2016 that match Accreditation accountIds
// by first collecting all the ujique accountIds into a set and ..
// then fetching all Business Plans that have those accountIds
set<ID> aIdSet = new set<ID>();
for (Accreditation__c accr: accrList)
    aIdSet.add(accr.account__c);
// Place the Business Plans into a map keyed by accountId
map<ID,Business_Plan__c> aIdToBpMap = new map<ID,Business_plan__c>();
for (Business_Plan__c bp : [[select Name, partner_account__c from Business_Plan__c 
                           where Year__c = '2016' AND 
                           Partner_Account__c IN :aIdSet])
    aIdToBPMap.put(bp.partner_account__c,bp);   


// finally, go back through the Accreditations and use our map to find the 
// business plan for the same accountId.
for(Accreditation__c accr : accrList)
   accr.Business_Plan__c = aIdToBpMap.containsKey(accr.account__c)
                 ? aIdToBpMap.get(accr.account__c).id  // the BP id
                 : null;


update accrList; // empty lists work fine here

Now, having said that, execute anonymous can run into issues with improperly implemented trigger recursion controls or DML limits because the entire set of Accreditation__c that qualify will get updated in one transaction; not broken into separate 200 record transactions such as done in Data Loader

To work around this, you can modify the first query to exclude any Accreditation__c that already have a business_plan__c set to non null and run the execute anonymous multiple times, using Limit 200