updating a records using external Id in Apex

Question

I copied the product data to another object named foo. I used only the name and externalId of product to copy it to Foo object. Foo's external id matches with product's external Id.

Some of my product's Name have changed. So I want to run a script to update corresponding foo records name. Since the external Id is same between product and foo , I can use that as a reference to upsert the records in Foo. Below is my code, however I get this error:

System.DmlException: Upsert failed. First exception on row 0; first error: DUPLICATE_VALUE, duplicate value found: Externald__c duplicates value on record with id: a1H370000016QkV: []

Apex code:

List<Product2> products = [SELECT Name,PrdExternalId,Product__r.PrdExternalId FROM Product2 WHERE Warehouse_SKU__c!=Null];
        system.debug('Product list ********* ' + products);
            List<Foo__c> flist = new List<foo__c>();
            for(Product2 p : products){
                foo__c f = new foo__C();
                f.Externald__c = p.PrdExternalId;
                f.Name = p.Name;
                f.foo__r.Externald__c = p.Product__r.PrdExternalId; ->> NULL POINTER EXCEPTION HERE
                flist.add(f);
                }
            upsert flist;

Part 2- When I try to use f.foo__r.Externald__c = p.Product__r.PrdExternalId; to reference the self lookup on foo__c object i get

System.NullPointerException: Attempt to de-reference a null object

Answer 1

You need to use the external id in the update call

upsert flist Externald__c;

For part two of your question, seeing that you want to relate the parent and child foo records you can t do it the way you were trying but I also think that you need to create the parent separately, however I think you either need to build a map to successfully relate the parent back to the child or have a process builder/flow do the parent mapping for you to relate the parent to the child accurately something like this:

List<Product2> products = [SELECT Name,PrdExternalId,Product__r.PrdExternalId FROM Product2 WHERE Warehouse_SKU__c!=Null];
    system.debug('Product list ********* ' + products);
        List<Foo__c> flist = new List<foo__c>();
        Map<String, Foo__c> parentfMap = new Map<String, foo__c>();
        for(Product2 p : products){
            foo__c f = new foo__C();
            f.Externald__c = p.PrdExternalId;
            f.Name = p.Name;
            if(!parentfMap.containsKey(p.PrdExternalId)){
                parentfMap.put(p.PrdExternalId, new Foo__c(Externald__c = p.Product__r.PrdExternalId))
            }
            
            flist.add(f);
            }
        upsert parentfMap.values() Externald__c; 
        for(Foo__c f : flist){
           f.foo__c = parentfMap.get(f.Externald__c).Id;
        }
        upsert flist Externald__c;

In theory that should work or be close to what you want to do

Answer 2

You have to specify the field which you want to use an external id.

So your syntax will be

upsert flist Externald__c;

You can even use Database methods

Database.upsert(flist , foo__c.Externald__c, true);

That being said, You cannot do

f.foo__r.Externald__c = p.Product__r.PrdExternalId;

As foo__r does not exist, foo__r would only exist when you query it.(Few exceptions lies). You have recrate a new Foo__C for parent to do your dml.

for(Product2 p : products){
    foo__c f = new foo__C();
    f.Externald__c = p.PrdExternalId;
    f.Name = p.Name;
    Foo__c parentFoo=new Foo__c();
    parentFoo.Externald__c = p.Product__r.PrdExternalId; 

    flist.add(f);
    flist.add(parentFoo);

}


 upsert flist Externald__c;