Using List Collection SOQL Value "variable does not exist"

Question

How can I access the values that are returned from the SOQL query?

List<Account> acc = [SELECT Id, Name, BillingCity FROM Account WHERE ID= :accountId];

Later in the method I update the records based on variables returned from a REST API callout.

acc.Name = name;
acc.BillingCity = city;
update acc;

I am getting errors that BillingCity and Name dont exist. If i use the following instead of the List then I no longer get any errors:

Account acc = [SELECT Id, Name, BillingCity FROM Account WHERE ID= :accountId];

It is my understanding that best practice is to use a list. Can someone answer if I need the list or can go without it. If its needed, then how can the values acc.Name and acc.BillingCity be accessed?

Answer 1

A List is basically an Array. Whenever you query for data you usually get a List back. The shorthand SObject obj = [SELECT ... FROM ... LIMIT 1]; can be used to get one record back, but error handling is a bit more difficult.

When you are absolutely sure that you will get a record back, the shorthand is fine, but if there is no record, you will get a System.QueryException: List has no rows for assignment to SObject. This error also directly shows what is going on in the background. At first the query will return a List, but then the automagic bit of Salesforce tries to pick a record from that list to assign it to your SObject variable, and fails.

So, if you are not absolutely sure that you will get one (and exactly one) record from your query, you should always work with a returned List.

List<SObject> objs = [SELECT ... FROM ...];

Now you have a chance to react to whatever the result may be. To make sure you have at least one record, check that:

if (objs.size () > 0) { ... }

This saves you from the dreaded System.QueryException: List has no rows for assignment to SObject.

But your other quest was how to access member variables of a record stored within a List. I can really recommend some reading on how to program Apex for that. But as a quick solution to your problem:

SObject obj = objs[0];

will give you one record out of the List of records.

You could also iterate over all contained records within one List:

for (SObject obj : objs) { ... }

This will give you each record as a single entry in the variable obj, allowing you to modify all records returned by a query.

Answer 2

It looks like you expect one record in your query, but you're storing it in a list?

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_methods_system_list.htm

Since it's a list, you can't simply just reference the whole list (which contains elements of whatever data type you specify). You need to either specify the specific element (acc[0], acc[1], acc[2], etc) or better yet, just loop through it without having to worry how many elements there are

List<Account> acc = [SELECT Id, Name, BillingCity FROM Account WHERE ID= :accountId];
for(Account accRec : acc){
    accRec.BillingCity = 'blah blah';
}
update acc;

If you only expect one, you could change it to the following

Account acc = [SELECT Id, Name, BillingCity FROM Account WHERE ID= :accountId];
acc.BillingCity = 'blah blah';
}

This lets you do what you were doing. The only thing is it'll throw an exception if the query does not return any records.

Depending on how you're getting "accountId" (in a trigger?), you could choose either option depending on if you're sure it'll always find the record.