Upserting Records

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 isn’t 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 not inserted or updated.

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

Examples

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'];
3for (Account a : acctsList) {
4    a.BillingCity = 'Mumbai';
5}
6Account newAcct = new Account(Name = 'Acme', BillingCity = 'San Francisco');
7acctsList.add(newAcct);
8try {
9    upsert 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 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);
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);
28
29      return uResults;
30   }
31}

1@isTest
2private class DmlSamplesTest {
3   public static testMethod void testUpsertLeads() {
4        /* We only need to test the insert side of upsert */
5      List<Lead> leads = new List<Lead>();
6
7      /* Create a set of leads for testing */
8      for(Integer i = 0;i < 100; i++) {
9         leads.add(new Lead(LastName = 'testLead', Company = 'testCompany'));
10      }
11
12      /* Switch to the runtime limit context */
13      Test.startTest();
14
15      /* Exercise the method */
16      List<Database.upsertResult> results = DmlSamples.upsertLeads(leads);
17
18      /* Switch back to the test context for limits */
19      Test.stopTest();
20
21      /* ID set for asserting the tasks were created as expected */
22      Set<Id> ids = new Set<Id>();
23
24      /* Iterate over the results, asserting success and adding the new ID
25         to the set for use in the comprehensive assertion phase below. */
26      for(Database.upsertResult result:results) {
27         System.assert(result.isSuccess());
28         ids.add(result.getId());
29      }
30
31      /* Assert that exactly one task exists for each lead that was inserted. */
32      for(Lead l:[SELECT Id, (SELECT Subject FROM Tasks) FROM Lead WHERE Id IN :ids]) {
33         System.assertEquals(1,l.tasks.size());
34      }
35   }
36}

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                       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 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}