upsert()

Creates new records and updates existing records; uses a custom field to determine the presence of existing records. In most cases, we recommend that you use upsert() instead of create() to avoid creating unwanted duplicate records (idempotent). Available in the API version 7.0 and later. You can process records for one more than object type in an create() or update() call, but all records must have the same object type in an upsert() call.

Note

Starting with API version 15.0, if you specify a value for a field that contains a string, and the value is too big for the field, the call fails and an error is returned. In previous versions of the API the value was truncated and the call succeeded. If you wish to keep the old behavior with versions 15.0 and later, use the AllowFieldTruncationHeader SOAP header.

Syntax

UpsertResult[] = 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 new record or update an existing record. On standard objects, this call can use the name of any field with the idLookup field property instead of the external ID.

Note
External ID fields cannot be used with merge().

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

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

If you are upserting a record for an object that has a custom field with both the External ID and Unique attributes selected (a unique index), you do not need any special permissions, because the Unique attribute prevents the creation of duplicates. If you are upserting a record for an object that has the External ID attribute selected but not the Unique attribute selected, (a non-unique index) your client application must have the permission “View All Data” to execute this call. Having this permission prevents the client application from using upsert() to insert an accidental duplicate record because it couldn’t see that the record existed.

Note
Matching by external ID is case-insensitive only if the external ID field has the Unique attribute and the Treat "ABC" and "abc" as duplicate values (case insensitive)) option selected. These options are selected in the Salesforce user interface during field creation. If this is the case, “ABC123” is matched with “abc123.” Before performing an operation, if you have external ID fields without the case-insensitive option selected, review your external IDs for any values that would be matched if case was not considered. If such values exist, you may want to modify them to make them unique, or select the case-sensitive option for your external ID fields. For more information about field attributes, see “Custom Field Attributes” in the Salesforce online help.

How Upsert Chooses to update() or create()

Upsert uses the external ID to determine whether it should create a new record or update an existing one:

Rollback on Error

The AllOrNoneHeader header allows you to roll back all changes unless all records are processed successfully. This header is available in API version 20.0 and later. The default behavior is to allow partial success of a call: records without errors are committed, while records with errors are marked as failed in the call results.

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 is not 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 is not 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 allows you to 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 do this, set the foreign key to an instance of the parent sObject that has only the external ID field specified. This external ID should match the external ID value on the parent record. Unlike create(), the parent record must already 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 will create 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

public void upsertForeignKeySample() {
   try {
      Opportunity newOpportunity = new Opportunity();
      newOpportunity.setName("UpsertOpportunity");
      newOpportunity.setStageName("Prospecting");
      Calendar dt = connection.getServerTimestamp().getTimestamp();
      dt.add(Calendar.DAY_OF_MONTH, 7);
      newOpportunity.setCloseDate(dt);
      newOpportunity.setMyExtId__c("UPSERTID001");

      // Parent Account record must already exist
      Account parentAccountRef = new Account();
      parentAccountRef.setMyExtId__c("SAP111111");
      newOpportunity.setAccount(parentAccountRef);

      SaveResult[] results = connection
            .upsert("MyExtId__c", new SObject[] { newOpportunity });         
   } catch (ConnectionException ce) {
      ce.printStackTrace();
   }
}

C# Example

public void upsertForeignKeySample()
{
   try
   {
      Opportunity newOpportunity = new Opportunity();
      newOpportunity.Name = "UpsertOpportunity";
      newOpportunity.StageName = "Prospecting";
      DateTime dt = (DateTime)binding.getServerTimestamp().timestamp;
      newOpportunity.CloseDate = dt.AddDays(7);
      newOpportunity.CloseDateSpecified = true;
      newOpportunity.MyExtId__c = "UPSERTID001";

      // Parent Account record must already exist
      Account parentAccountRef = new Account();
      parentAccountRef.MyExtId__c = "SAP111111";
      newOpportunity.Account = parentAccountRef;

      SaveResult[] results = binding
            .upsert("MyExtId", new sObject[] { newOpportunity });
   }
   catch (SoapException e)
   {
      Console.WriteLine("An unexpected error has occurred: " +
                                 e.Message + "\n" + e.StackTrace);
   }
}

upsert() and Polymorphic Foreign Keys

A polymorphic key is an ID that can refer to more than one type of object as a parent. For example, the OwnerId field on the Case object can reference either a User or a Group. Similarly, the WhoID field on the Task object can reference either a Contact or a Lead.

With the Enterprise WSDL, such polymorphic foreign key fields are defined as a Name sObject type, so it is not possible to pass in the correct sObject in an upsert() call. However, with the Partner WSDL all foreign key fields—including polymorphic ones—are of type sObject , which allows upsert() calls on such fields.
Important
If your organization has SOQL Polymorphism enabled, polymorphic relationship fields point to sObjects and not Names in the Enterprise WSDL; therefore, you can pass the correct sObject in a call. SOQL Polymorphism is currently available as a Developer Preview. For more information on enabling SOQL Polymorphism for your organization, contact salesforce.com, inc.

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.

public void upsertRecords() {
   SObject[] upserts = new Account[2];

   Account upsertAccount1 = new Account();
   upsertAccount1.setName("Begonia");
   upsertAccount1.setIndustry("Education");
   upsertAccount1.setMyExtId__c("1111111111");
   upserts[0] = upsertAccount1;

   Account upsertAccount2 = new Account();
   upsertAccount2 = new Account();
   upsertAccount2.setName("Bluebell");
   upsertAccount2.setIndustry("Technology");
   upsertAccount2.setMyExtId__c("2222222222");
   upserts[1] = upsertAccount2;

   try {
      // Invoke the upsert call and save the results.
      // Use External_Id custom field for matching records.
      UpsertResult[] upsertResults = connection.upsert(
                                  "MyExtId__c", upserts);
      for (UpsertResult result : upsertResults) {
         if (result.isSuccess()) {
            System.out.println("\nUpsert succeeded.");
            System.out.println((result.isCreated() ? "Insert" : "Update")
                  + " was performed.");
            System.out.println("Account ID: " + result.getId());
         } else {
            System.out.println("The Upsert failed because: "
                  + result.getErrors()[0].getMessage());
         }
      }
   } catch (ConnectionException ce) {
      ce.printStackTrace();
   }
}

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.

public void upsertRecords()
{
   sObject[] upserts = new Account[2];

   Account upsertAccount1 = new Account();
   upsertAccount1.Name = "Begonia";
   upsertAccount1.Industry = "Education";
   upsertAccount1.MyExtId__c = "1111111111";
   upserts[0] = upsertAccount1;

   Account upsertAccount2 = new Account();
   upsertAccount2 = new Account();
   upsertAccount2.Name = "Bluebell";
   upsertAccount2.Industry = "Technology";
   upsertAccount2.MyExtId__c = "2222222222";
   upserts[1] = upsertAccount2;

   try
   {
      // Invoke the upsert call and save the results.
      // Use External_Id custom field for matching records. UpsertResult[] upsertResults = binding.upsert("MyExtId__c", upserts); foreach (UpsertResult result in upsertResults) { if (result.success) { Console.WriteLine("\nUpsert succeeded."); Console.WriteLine( (result.created ? "Insert" : "Update") + " was performed." ); Console.WriteLine("Account ID: " + result.id); } else { Console.WriteLine("The Upsert failed because: " + result.errors[0].message); } } } catch (SoapException e) { Console.WriteLine("An unexpected error has occurred: " + e.Message + "\n" + e.StackTrace); } }

Arguments

Name Type Description
ExternalIDFieldName string Contains the name of the field on this object with the external ID field attribute for custom objects or the idLookup field property for standard objects. The idLookup field property is usually on a field that is the object's ID field or name field, but there are exceptions, so check for the presence of the property in the object you wish to upsert().
sObjects sObject[] Array of one or more records (maximum of 200) to create or update.

Response

UpsertResult[]

Faults

InvalidSObjectFault

UnexpectedErrorFault

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