Cannot update lookup reference by External Id, if current reference is not null

Question

Scenario:

Product__c product = [SELECT Id, ProcurementGroup__r.Number__c FROM Product__c LIMIT 1]
product.ProcurementGroup__r = new ProcurementGroup__c(ExternalId__c = '123');
update product;

I am referencing a lookup record by external Id. This code works, if ProcurementGroup__c lookup is empty on the record. But if it already references something, I get this error:

FATAL_ERROR System.DmlException: Update failed. First exception on row 0 with id a07D000001D6BwGIAV; first error: INVALID_FIELD, Cannot specify both an external ID reference ProcurementGroup__r and a salesforce id, ProcurementGroup__c: []

As a workaround I tried to set ProcurementGroup__c to null:

product.ProcurementGroup__c = null;
product.ProcurementGroup__r = new ProcurementGroup__c(ExternalId__c = '123');
update product;

But I still get the same error. The only other workaround I can think of is to update object twice, first to remove reference, then to specify new reference.

Anyone has a better idea? Appreciate any suggestions.

Answer 1

Found solution

Issue cause: SOQL query reference ProcurementGroup__r.Number__c. Looks like queried record automatically includes ProcurementGroup__c even though it is not queried. In real code I need access to that value, so I cannot remove it from the query.

Solution:

Product__c tempProduct = new Product__c(Id = product.Id);
tempProduct.ProcurementGroup__r = new ProcurementGroup__c(ExternalId__c = '123');
update tempProduct;

By initializing new instance of the record, I am getting read of ProcurementGroup__r.Number__c value, so the update can successfully change the reference.