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.
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.
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.
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.
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. |
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
}
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).
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()); } }
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()); } } }