upsert vs update vs insert

  • I'm trying to understand when to use insert/update/upsert. As I understand, upsert is a combination of insert and update. I tend to use upsert most often as I do not have to check whether an object is a new object or an existing object. I do not see a reason to use update or insert since upsert calls would work for these calls. Is there a reason I should not be using upsert and instead use the other two?

    If any of the below answers addressed your problem, please consider accepting one by clicking on the check mark/tick to the left of the answer, turning it green. This marks the question as resolved to your satisfaction, and awards reputation both to you and the person who answered. If you have >= 15 reputation points, you may also upvote the answer if you wish. There is no obligation to do either.

  • In general, there's no real penalty for using upsert as opposed to insert and update. One notable limitation, however, is that upsert does not support generic SObject[] lists or generic SObject records. This also means you can't take advantage of combining DML calls to reduce governor limit usage. For example, consider this code:

    Account a = new Account(Name='Test');
    insert a;
    Contact c = new Contact(LastName='Test', AccountId=a.Id);
    Opportunity o = new Opportunity(Name='Test', CloseDate=Date.today(), Amount=1000, StageName='Prospecting', AccountId=a.Id);
    insert new SObject[] { c, o };
    

    In this case, we created 3 records using just 2 DML statements of our allowed 150. However, with upsert, we can't do this, and end up using 3 DML statements. For many cases, it doesn't matter, but reducing DML usage can be important during unit tests that involve setting up a many different types of records. In a larger scenario, this could be significant, as you can combine up to ten different types of records in a single SObject list, saving up to 9 DML calls every time you use this technique.

    Besides that, in the more general case, upsert is not self-documenting. By that, I mean that overusing upsert makes it harder to tell if it's really an upsert, or always an insert, or always an update. You should only use upsert when it really is a situation where it may be an insert or update. Using upsert unnecessarily throws doubt at those that will have to maintain your code later. This is probably the most important consideration.

    Edit: One additional consideration. I've benchmarked this before, and it uses roughly the same CPU time as deciding if you want to do an insert or update manually (e.g. writing an if statement with both insert and update branches). This means that if you know it's an insert or update, you'll shave off a few milliseconds by using the appropriate DML statement. Since we only have a limit of 150 DML statements, it doesn't save much time, perhaps maybe a 1/3 of a second if you were near the limit, but it is a performance hit.

  • INSERT

    • Always use to create new records
    • Support generic SObject type insert

      Note:

      • Must NOT have id in the Sobject instance

    UPDATE:

    • Always use to update the record
    • Support generic Sobject type updates

      Note:

      • Must have ID in the Sobject instance

    UPSERT

    • Always used in case when INSERT and UPSERT both. It is possible on Sobject depending on ID is present in instance or not.
    • If ID is present it will UPDATE otherwise, INSERT
    • We can use External Id type to update the Sobject in case the instance don't have ID in it.
      Ex: upsert SObjectList External_Id__c

      Note:

      • It does not support generic Sobject type. You will have to Specify the CustomObject.
  • If you are sure that, you are migrating data into a fresh organization there use insert operation.

    If you are not sure that, whether data exists in that environment then go for upsert.

    In upsert operation Salesforce internally validates the data based on Object's Id or External Id. So, upsert takes little bit longer time than insert or update.

    Using the upsert operation, you can either insert or update an existing record in one call. To determine whether a record already exists, the upsert statement or Database method uses the record’s ID as the key to match records, a custom external ID field, or a standard field with the idLookup attribute set to true.

    • If the key is not matched, then a new object record is created.
    • If the key is matched once, then the existing object record is updated.
    • If the key is matched multiple times, then an error is generated and the object record is neither inserted or updated.

    For more information, refer Upserting Records

  • Update refreshes existing records only. Upsert refreshes existing records and inserts new records if it does not find a match.

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM

Tags used