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.
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.”
Note
The following example updates the city name for all existing accounts in the city formerly known as Bombay, and also inserts a new account in San Francisco:
1Account[] acctsList = [SELECT Id, Name, BillingCity
2 FROM Account WHERE BillingCity = 'Bombay' WITH USER_MODE];
3for (Account a : acctsList) {
4 a.BillingCity = 'Mumbai';
5}
6Account newAcct = new Account(Name = 'Acme', BillingCity = 'San Francisco');
7acctsList.add(newAcct);
8try {
9 upsert as user acctsList;
10} catch (DmlException e) {
11 // Process exception here
12}For more information on processing DmlExceptions, see Bulk DML Exception Handling.
Note
This next example uses the Database.upsert method to upsert a collection of leads that are passed in. This example allows for partial processing of records, that is, in case some records fail processing, the remaining records are still inserted or updated. It iterates through the results and adds a task to each record that was processed successfully. The task sObjects are saved in a list, which is then bulk inserted. This example is followed by a test class that contains a test method for testing the example.
1/* This class demonstrates and tests the use of the
2 * partial processing DML operations */
3
4public with sharing class DmlSamples {
5
6 /* This method accepts a collection of lead records and
7 creates a task for the owner(s) of any leads that were
8 created as new, that is, not updated as a result of the upsert
9 operation */
10 public static List<Database.upsertResult> upsertLeads(List<Lead> leads) {
11
12 /* Perform the upsert. In this case the unique identifier for the
13 insert or update decision is the Salesforce record ID. If the
14 record ID is null the row will be inserted, otherwise an update
15 will be attempted. */
16 List<Database.upsertResult> uResults = Database.upsert(leads,false, AccessLevel.USER_MODE);
17
18 /* This is the list for new tasks that will be inserted when new
19 leads are created. */
20 List<Task> tasks = new List<Task>();
21 for(Database.upsertResult result:uResults) {
22 if (result.isSuccess() && result.isCreated())
23 tasks.add(new Task(Subject = 'Follow-up', WhoId = result.getId()));
24 }
25
26 /* If there are tasks to be inserted, insert them */
27 Database.insert(tasks, AccessLevel.USER_MODE);
28
29 return uResults;
30 }
31}1@IsTest
2private with sharing class DmlSamplesTest {
3 @IsTest
4 public static void testUpsertLeads() {
5 /* We only need to test the insert side of upsert */
6 List<Lead> leads = new List<Lead>();
7
8 /* Create a set of leads for testing */
9 for(Integer i = 0;i < 100; i++) {
10 leads.add(new Lead(LastName = 'testLead', Company = 'testCompany'));
11 }
12
13 /* Switch to the runtime limit context */
14 Test.startTest();
15
16 /* Exercise the method */
17 List<Database.upsertResult> results = DmlSamples.upsertLeads(leads);
18
19 /* Switch back to the test context for limits */
20 Test.stopTest();
21
22 /* ID set for asserting the tasks were created as expected */
23 Set<Id> ids = new Set<Id>();
24
25 /* Iterate over the results, asserting success and adding the new ID
26 to the set for use in the comprehensive assertion phase below. */
27 for(Database.upsertResult result:results) {
28 Assert.isTrue(result.isSuccess());
29 ids.add(result.getId());
30 }
31
32 /* Assert that exactly one task exists for each lead that was inserted. */
33 for(Lead l:[SELECT Id, (SELECT Subject FROM Tasks) FROM Lead WHERE Id IN :ids WITH USER_MODE]) {
34 Assert.areEqual(1,l.tasks.size());
35 }
36 }
37}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 Execution Governors and Limits).
This 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. Before running the sample, create a custom text field on the Asset object named Line_Item_Id__c and mark it as an external ID. For information on custom fields, see Salesforce Help.
External ID fields used in upsert calls must be unique or the user must have the View All Data permission.
Note
1public void upsertExample() {
2 Opportunity opp = [SELECT Id, Name, AccountId,
3 (SELECT Id, PricebookEntry.Product2Id, PricebookEntry.Name
4 FROM OpportunityLineItems)
5 FROM Opportunity
6 WHERE HasOpportunityLineItem = true
7 WITH USER_MODE LIMIT 1];
8
9 Asset[] assets = new Asset[]{};
10
11 // Create an asset for each line item on the opportunity
12 for (OpportunityLineItem lineItem:opp.OpportunityLineItems) {
13
14 //This code populates the line item Id, AccountId, and Product2Id for each asset
15 Asset asset = new Asset(Name = lineItem.PricebookEntry.Name,
16 Line_Item_ID__c = lineItem.Id,
17 AccountId = opp.AccountId,
18 Product2Id = lineItem.PricebookEntry.Product2Id);
19
20 assets.add(asset);
21 }
22
23 try {
24 upsert as user assets Line_Item_ID__c; // This line upserts the assets list with
25 // the Line_Item_Id__c field specified as the
26 // Asset field that should be used for matching
27 // the record that should be upserted.
28 } catch (DmlException e) {
29 System.debug(e.getMessage());
30 }
31}
j