upsert()

Syntax

1UpsertResult[] = connection.upsert(String externalIdFieldName, sObject[] sObjects);

Usage

Upsert is a merging of the words insert and update. This call is available for objects if the object has an external ID field or a field with the idLookup field property.

On custom objects, this call uses an indexed custom field called an external ID to determine whether to create a record or update an existing record. On standard objects, this call can use the name of any field with the idLookup instead of the external ID.

For more information about adding custom fields, including external ID fields, to objects, see the “Adding Fields” topic in Salesforce Help.

Using this call can dramatically reduce how many calls you must make, particularly when:

• You’re integrating your organization's Salesforce data with ERP (enterprise resource planning) systems such as accounting and manufacturing.
• You’re importing data and want to prevent the creation of duplicate objects.

If you’re upserting a record for an object that has a custom field with both the External ID and Unique attributes selected (a unique index), you don’t need any special permissions, because the Unique attribute prevents the creation of duplicates. If you’re upserting a record for an object that has the External ID non-unique index) your client application must have the permission “View All Data” to execute this call.

How Upsert Chooses to update() or create()

Upsert uses the external ID to determine whether it creates a record or updates an existing one:

• If the external ID isn’t matched, then a new record is created.
• If the external ID is matched one time, then the existing record is updated.
• If the external ID is matched multiple times, then an error is reported.
• When batch updating multiple records where the external ID is the same for two or more records in your batch call, those records are marked as errors in the UpsertResult file. The records are neither created.

Rollback on Error

The AllOrNoneHeader header lets you roll back all changes unless all records are processed successfully. This header is available in API version 20.0 and later.

Automatic Subscriptions for Chatter Feeds

To subscribe to records they create, users must enable the Automatically follow records that I create option in their personal settings. If users have automatic subscriptions enabled, they automatically follow the records they create and see changes to those records in their Chatter feed on the Home tab.

When you update the owner of a record, the new owner isn’t automatically subscribed to the record, unless the new owner has automatic subscriptions for records enabled in his or her Chatter feed settings. The previous owner isn’t automatically unsubscribed. If the new owner has automatic subscriptions for records enabled, the new and previous owners both see any changes to the record in their news feed.

A user can subscribe to a record or to another user. Changes to the record and updates from the users are displayed in the Chatter feed on the user's home page, which is a useful way to stay up to date with other users and with changes made to records in Salesforce. Feeds are available in API version 18.0 and later.

upsert() and Foreign Keys

You can use external ID fields as a foreign key, which lets you create or update a record and relate it to another existing record in a single step instead of querying the parent record ID first. To use an external ID field as a foreign key, set the foreign key to an instance of the parent sObject that has only the external ID field specified. This external ID must match the external ID value on the parent record. Unlike create(), the parent record must currently exist when using upsert() to create or update a child record related by a foreign key.

The following Java and C# examples upsert an opportunity. In this case, the opportunity doesn't exist in the database, so the upsert() call creates it. The opportunity references an existing account. Rather than specify the account ID, which would require a separate query to obtain, we specify an external ID for the account, in this example the MyExtId__c custom field.

Java Example

1public void upsertForeignKeySample() {
2   try {
3      Opportunity newOpportunity = new Opportunity();
4      newOpportunity.setName("UpsertOpportunity");
5      newOpportunity.setStageName("Prospecting");
6      Calendar dt = connection.getServerTimestamp().getTimestamp();
7      dt.add(Calendar.DAY_OF_MONTH, 7);
8      newOpportunity.setCloseDate(dt);
9      newOpportunity.setMyExtId__c("UPSERTID001");
10
11      // Parent Account record must already exist
12      Account parentAccountRef = new Account();
13      parentAccountRef.setMyExtId__c("SAP111111");
14      newOpportunity.setAccount(parentAccountRef);
15
16      SaveResult[] results = connection
17            .upsert("MyExtId__c", new SObject[] { newOpportunity });         
18   } catch (ConnectionException ce) {
19      ce.printStackTrace();
20   }
21}

C# Example

1public void upsertForeignKeySample()
2{
3   try
4   {
5      Opportunity newOpportunity = new Opportunity();
6      newOpportunity.Name = "UpsertOpportunity";
7      newOpportunity.StageName = "Prospecting";
8      DateTime dt = (DateTime)binding.getServerTimestamp().timestamp;
9      newOpportunity.CloseDate = dt.AddDays(7);
10      newOpportunity.CloseDateSpecified = true;
11      newOpportunity.MyExtId__c = "UPSERTID001";
12
13      // Parent Account record must already exist
14      Account parentAccountRef = new Account();
15      parentAccountRef.MyExtId__c = "SAP111111";
16      newOpportunity.Account = parentAccountRef;
17
18      SaveResult[] results = binding
19            .upsert("MyExtId", new sObject[] { newOpportunity });
20   }
21   catch (SoapException e)
22   {
23      Console.WriteLine("An unexpected error has occurred: " +
24                                 e.Message + "\n" + e.StackTrace);
25   }
26}

Sample Code—Java

This sample upserts two accounts using a custom external ID field called MyExtId__c. The upsert() call matches the accounts based on the MyExtId__c field in order to determine whether to create or update the accounts. Before running this sample, change the MyExtId__c field name to an existing custom ID field name in your org.

1public void upsertRecords() {
2   SObject[] upserts = new Account[2];
3
4   Account upsertAccount1 = new Account();
5   upsertAccount1.setName("Begonia");
6   upsertAccount1.setIndustry("Education");
7   upsertAccount1.setMyExtId__c("1111111111");
8   upserts[0] = upsertAccount1;
9
10   Account upsertAccount2 = new Account();
11   upsertAccount2 = new Account();
12   upsertAccount2.setName("Bluebell");
13   upsertAccount2.setIndustry("Technology");
14   upsertAccount2.setMyExtId__c("2222222222");
15   upserts[1] = upsertAccount2;
16
17   try {
18      // Invoke the upsert call and save the results.
19      // Use External_Id custom field for matching records.
20      UpsertResult[] upsertResults = connection.upsert(
21                                  "MyExtId__c", upserts);
22      for (UpsertResult result : upsertResults) {
23         if (result.isSuccess()) {
24            System.out.println("\nUpsert succeeded.");
25            System.out.println((result.isCreated() ? "Insert" : "Update")
26                  + " was performed.");
27            System.out.println("Account ID: " + result.getId());
28         } else {
29            System.out.println("The Upsert failed because: "
30                  + result.getErrors()[0].getMessage());
31         }
32      }
33   } catch (ConnectionException ce) {
34      ce.printStackTrace();
35   }
36}

Sample Code—C#

This sample upserts two accounts using a custom external ID field called MyExtId__c. The upsert() call matches the accounts based on the MyExtId__c field in order to determine whether to create or update the accounts. Before running this sample, change the MyExtId__c field name to an existing custom ID field name in your org.

1public void upsertRecords()
2{
3   sObject[] upserts = new Account[2];
4
5   Account upsertAccount1 = new Account();
6   upsertAccount1.Name = "Begonia";
7   upsertAccount1.Industry = "Education";
8   upsertAccount1.MyExtId__c = "1111111111";
9   upserts[0] = upsertAccount1;
10
11   Account upsertAccount2 = new Account();
12   upsertAccount2 = new Account();
13   upsertAccount2.Name = "Bluebell";
14   upsertAccount2.Industry = "Technology";
15   upsertAccount2.MyExtId__c = "2222222222";
16   upserts[1] = upsertAccount2;
17
18   try
19   {
20      // Invoke the upsert call and save the results.
21      // Use External_Id custom field for matching records.
22      UpsertResult[] upsertResults =
23            binding.upsert("MyExtId__c", upserts);
24      foreach (UpsertResult result in upsertResults)
25      {
26         if (result.success)
27         {
28            Console.WriteLine("\nUpsert succeeded.");
29            Console.WriteLine(
30                  (result.created ? "Insert" : "Update") +
31                  " was performed."
32            );
33            Console.WriteLine("Account ID: " + result.id);
34         }
35         else
36         {
37            Console.WriteLine("The Upsert failed because: " +
38                  result.errors[0].message);
39         }
40      }
41   }
42   catch (SoapException e)
43   {
44      Console.WriteLine("An unexpected error has occurred: " +
45                                 e.Message + "\n" + e.StackTrace);
46   }
47}

Arguments

Response

UpsertResult[]

Faults

InvalidSObjectFault

UnexpectedErrorFault