0

Consider the below code. I thought the way upsert DML works is that if it sees that there is a new record with matching key (here in this case there is already an account with Name = Acme and BillingCity = Austin), it will not insert the new account in line number 6 and will just update the existing account with the Phone = '412 9012333'. But however it doesn't work that way and it inserts the account if it finds a new Account statement. Please correct me if my understanding is correct and whether I am missing something.

1 List<Account> newAcct = new List<Account>();
2 newAcct = [SELECT Id, Name, BillingCity  FROM Account WHERE Name = 'Acme'];
3 for(Account c : newAcct){
4    c.BillingCity = 'Austin';
5 }
6 Account newAcct2 = new Account(Name ='Acme', BillingCity = 'Austin', Phone = '412 9012333');
7 newAcct.add(newAcct2);
8 upsert newAcct;
1

That's not how upsert works. It works by using Id, or an External ID, to determine if the record should be inserted or updated. You would need some sort of external ID field to work with:

List<Account> newAcct = [SELECT Id, Name, BillingCity  FROM Account WHERE Name = 'Acme'];
Integer externalIdCounter = 0;
for(Account c : newAcct) {
  c.External_Id__c = (externalIdCounter++)+'';
}
update newAcct;
Account newAcct2 = new Account(External_Id__c='0', Name ='Acme', BillingCity = 'Austin', Phone = '412 9012333');
newAcct.add(newAcct2);
upsert newAcct Account.External_Id__c;

Upsert does not use multiple keys/composite keys. It requires you to set up a special field for the purpose of upserting, or using the Salesforce ID as the key (which is the default if you do not specify a field).

8
  • Thank you. Does that mean I need to use Id for standard or custom objects and cannot use any other fields such as Name? External id should be used if we need to create an external relationship right? – Krishnamurthy Dec 24 '20 at 13:28
  • @Krishnamurthy Right, you need a custom field to use as an External ID. The REST API can use the Name field, though. – sfdcfox Dec 24 '20 at 13:33
  • thank you. So from your explanation I need to give id if I need to update an existing record and insert new records for new records right? – Krishnamurthy Dec 24 '20 at 13:45
  • I found that you can use Account.fields.Name to specify the key. I’m surprised you didn’t mention this? – Krishnamurthy Dec 24 '20 at 14:05
  • @Krishnamurthy I thought you could, but I tried it and it didn't want to take, so I figured they changed something. If it's working for you, then that would work just fine. Just remember each name must be unique or you'll get an error. – sfdcfox Dec 24 '20 at 14:32

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.