Upsert Operation

The upsert DML operation creates new sObject records and updates existing sObject records within a single statement,using an optional custom field to determine the presence of existing objects.

DML Statement Syntax

upsert sObject​​ opt_external_id

upsert sObject[]​​ opt_external_id

opt_external_id​​ is an optional variable that specifies the custom field that should be used to match records that already exist in your organization's data. This custom field must be created with the External Id attribute selected. Additionally, if the field does not have the Unique attribute selected, the context user must have the “View All” object-level permission for the target object or the “View All Data” permission so that upsert does not accidentally insert a duplicate record.

If opt_external_id​​ is not specified, the sObject record's ID field is used by default.

Note
Custom field matching is case-insensitive only if the custom field has the Unique and Treat "ABC" and "abc" as duplicate values (case insensitive) attributes selected as part of the field definition. If this is the case, “ABC123” is matched with “abc123.” For more information, see Creating Custom Fields.

Database Method Syntax

The optional External_ID_Field parameter is an optional variable that specifies the custom field that should be used to match records that already exist in your organization's data. This custom field must be created with the External Id attribute selected. Additionally, if the field does not have the Unique attribute selected, the context user must have the “View All” object-level permission for the target object or the “View All Data” permission so that upsert does not accidentally insert a duplicate record.

The External_ID_Field is of type Schema.SObjectField, that is, a field token. Find the token for the field by using the fields special method. For example, Schema.SObjectField f = Account.Fields.MyExternalId.

If External_ID_Fieldis not specified, the sObject record's ID field is used by default.

Note
Custom field matching is case-insensitive only if the custom field has the Unique and Treat "ABC" and "abc" as duplicate values (case insensitive) attributes selected as part of the field definition. If this is the case, “ABC123” is matched with “abc123.” For more information, see Creating Custom Fields.

The optional opt_allOrNoneparameter specifies whether the operation allows partial success. If you specify false for this parameter and a record fails, the remainder of the DML operation can still succeed. This method returns a result object that can be used to verify which records succeeded, which failed, and why.

How Upsert Chooses to Insert or Update

Upsert uses the sObject record's primary key (or the external ID, if specified) to determine whether it should create a new object record or update an existing one:
  • 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.

Rules and Guidelines

When upserting sObject records, consider the following rules and guidelines:
  • Certain sObjects cannot be inserted or updated. To insert an sObject record, the createable property of the sObject must be set to true. To update an sObject record, the updateable property of the sObject must be set to true.
  • You must supply a non-null value for all required fields on any record that will be inserted.
  • The ID of an sObject record cannot be modified, but related record IDs can. This action is interpreted as an update.
  • The upsert statement automatically modifies the values of certain fields such as LastModifiedDate, LastModifiedById, and SystemModstamp. You cannot explicitly specify these values in your Apex.
  • Each upsert statement consists of two operations, one for inserting records and one for updating records. Each of these operations is subject to the runtime limits for insert and update, respectively. For example, if you upsert more than 10,000 records and all of them are being updated, you receive an error. (See Understanding Execution Governors and Limits)
  • The upsert statement can only set the ID of related sObject records. Fields on related records cannot be modified with upsert. For example, if updating an existing contact, you can specify the contact's related account record by setting the value of the AccountId field. However, you cannot change the account's name without updating the account itself with a separate DML statement.
  • Upsert is not supported with some sObjects. See sObjects That Do Not Support DML Operations.
  • You can use foreign keys to upsert sObject records if they have been set as reference fields. For more information, see http://www.salesforce.com/us/developer/docs/api/index_CSH.htm#field_types.htm in the Web Services API Developer's Guide.

UpsertResult Object

An array of Database.UpsertResult objects is returned with the upsert database method. Each element in the UpsertResult array corresponds to the sObject array passed as the sObject[] parameter in the upsert database method, that is, the first element in the UpsertResult array matches the first element passed in the sObject array, the second element corresponds with the second element, and so on. If only one sObject is passed in, the UpsertResults array contains a single element.

An UpsertResult object has the following methods:

Name Type Description
getErrors Database.Error [] If an error occurred, an array of one or more database error objects providing the error code and description. For more information, see Database Error Object Methods.
getId ID The ID of the sObject you were trying to update or insert. If this field contains a value, the object was successfully updated or inserted. If this field is empty, the operation was not successful for that object.
isCreated Boolean A Boolean value that is set to true if the record was created, false if the record was updated.
isSuccess Boolean A Boolean value that is set to true if the DML operation was successful for this object, false otherwise.

DML Statement Examples

The following example updates the city name for all existing accounts located in the city formerly known as Bombay, and also inserts a new account located in San Francisco:
Account[] acctsList = [SELECT Id, Name, BillingCity
                        FROM Account WHERE BillingCity = 'Bombay'];
for (Account a : acctsList) {
    a.BillingCity = 'Mumbai';
}
Account newAcct = new Account(Name = 'Acme', BillingCity = 'San Francisco');
acctsList.add(newAcct);
try {
    upsert acctsList;
} catch (DmlException e) {
    // Process exception here 
    
}
Note
For more information on processing DmlExceptions, see Bulk DML Exception Handling.

This next example uses upsert and an external ID field Line_Item_Id__c on the Asset object to maintain a one-to-one relationship between an asset and an opportunity line item. Use of upsert with an external ID can reduce the number of DML statements in your code, and help you to avoid hitting governor limits (see Understanding Execution Governors and Limits).

Note
This example requires the addition of a custom text field on the Asset object named Line_Item_Id__c. This field must be flagged as an external ID. For information on custom fields, see the Salesforce online help.
public void upsertExample() {
    Opportunity opp = [SELECT Id, Name, AccountId, 
                              (SELECT Id, PricebookEntry.Product2Id, PricebookEntry.Name 
                               FROM OpportunityLineItems)
                       FROM Opportunity 
                       WHERE HasOpportunityLineItem = true 
                       LIMIT 1]; 

    Asset[] assets = new Asset[]{}; 

    // Create an asset for each line item on the opportunity 
    
    for (OpportunityLineItem lineItem:opp.OpportunityLineItems) {

        //This code populates the line item Id, AccountId, and Product2Id for each asset 
    
        Asset asset = new Asset(Name = lineItem.PricebookEntry.Name,
                                Line_Item_ID__c = lineItem.Id,
                                AccountId = opp.AccountId,
                                Product2Id = lineItem.PricebookEntry.Product2Id);

        assets.add(asset);
    }
 
    try {
        upsert assets Line_Item_ID__c;  // This line upserts the assets list with 
    
                                        // the Line_Item_Id__c field specified as the  
    
                                        // Asset field that should be used for matching 
    
                                        // the record that should be upserted.  
    
    } catch (DmlException e) {
        System.debug(e.getMessage());
    }
}

DML Statement Example

The following is an example that uses the database upsert method:

/* This class demonstrates and tests the use of the
 * partial processing DML operations */ 
     

public class dmlSamples {

   /* This method accepts a collection of lead records and 
      creates a task for the owner(s) of any leads that were 
      created as new, that is, not updated as a result of the upsert
      operation */ 
    
   public static List<Database.upsertResult> upsertLeads(List<Lead> leads)  {

      /* Perform the upsert. In this case the unique identifier for the
         insert or update decision is the Salesforce record ID. If the 
         record ID is null the row will be inserted, otherwise an update
         will be attempted. */ 
    
      List<Database.upsertResult> uResults = Database.upsert(leads,false);

      /* This is the list for new tasks that will be inserted when new 
         leads are created. */ 
    
      List<Task> tasks = new List<Task>();
      for(Database.upsertResult result:uResults) {
         if (result.isSuccess() && result.isCreated()) 
              tasks.add(new Task(Subject = 'Follow-up', WhoId = result.getId()));
      }

      /* If there are tasks to be inserted, insert them */ 
    
      Database.insert(tasks);

      return uResults;
   }

   public static testMethod void testUpsertLeads() {
        /* We only need to test the insert side of upsert */ 
    
      List<Lead> leads = new List<Lead>();

      /* Create a set of leads for testing */ 
    
      for(Integer i = 0;i < 100; i++) {
         leads.add(new Lead(LastName = 'testLead', Company = 'testCompany'));
      }

      /* Switch to the runtime limit context */ 
    
      Test.startTest();

      /* Exercise the method */ 
    
      List<Database.upsertResult> results = DmlSamples.upsertLeads(leads);

      /* Switch back to the test context for limits */ 
    
      Test.stopTest();

      /* ID set for asserting the tasks were created as expected */ 
    
      Set<Id> ids = new Set<Id>();

      /* Iterate over the results, asserting success and adding the new ID
         to the set for use in the comprehensive assertion phase below. */ 
    
      for(Database.upsertResult result:results) {
         System.assert(result.isSuccess());
         ids.add(result.getId());
      }

      /* Assert that exactly one task exists for each lead that was inserted. */ 
    
      for(Lead l:[SELECT Id, (SELECT Subject FROM Tasks) FROM Lead WHERE Id IN :ids]) {
         System.assertEquals(1,l.tasks.size());
      }

   }

}
© Copyright 2000–2012 salesforce.com, inc. All rights reserved.
Various trademarks held by their respective owners.