I am getting FATAL_ERROR|System.QueryException: List has no rows for assignment to SObject. I know that it is because it is not returning any rows in the SOQL query but what I want is that if it returns any row, it should update ALO field on contact object, if it is null then ALO field should be set to null.

Here is my code:

ziptoLOD = [SELECT Id, Name, LOD__c
            FROM Zip_3_to_LOD__c
            WHERE Name =: contact.Extract_Zipcode__c ];

Zip_3_to_LOD__c ziptoLOD2 = (ziptoLOD != null) ? ziptoLOD : null;

if (ziptoLOD2 == null) {
    contact.ALO__c = ' ';   
} else {
    System.debug(System.LoggingLevel.ERROR, 'Zip to LOD' + ziptoLOD2);
    contact.ALO__c = ziptoLOD2.LOD__c;

My code gets stuck at the SOQL query and gives me the list has no rows error.


The Apex Developer Guide explains this error as follows:

SOQL queries can be used to assign a single sObject value when the result list contains only one element. When the L-value of an expression is a single sObject type, Apex automatically assigns the single sObject record in the query result list to the L-value. A runtime exception results if zero sObjects or more than one sObject is found in the list.

What that means is when you assign the result of a SOQL query to a single sObject, and there isn't exactly one sObject returned by the query, you get an exception.

Fortunately, this is easy to fix. Simply assign the SOQL query result to a list instead of a single sObject, and ensure your logic inspects the size of the returned list when deciding on the next step.

List<Zip3_to_LOD__c> results = [
                    SELECT Id, Name, LOD__c
                    FROM Zip_3_to_LOD__c
                    WHERE Name =: contact.Extract_Zipcode__c

if (results.size() > 0) {
    ziptoLOD2 = results[0];
} else {
    ziptoLOD2 = null;

Just complementing David's answer, please keep in mind that, instead of

if (results.size() > 1) {
   ziptoLOD = results[0];
   contact.ALO__c = ziptoLOD.LOD__c;

You could use:

if (!results.isEmpty()) {
   ziptoLOD = results[0];
   contact.ALO__c = ziptoLOD.LOD__c;

Here you can find more details about: list.IsEmpty() vs list.Size() > 0 vs list != null

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.