Upsert() is a hidden gem in the API.    Our goal for upsert() was to allow you to push data from an external database into Salesforce.com using without having your external system know anything about salesforce.com IDs.   Conceptually, we wanted to let you switch from sending updates that looked like this:

to updates that looked like this:

There are three key problems to solve to enable this : 

1. We have to avoid having you choose between create() or update(), since you don’t know if the record already exists
2. We have to help you match the record from the external database to one within salesforce.com using the external system’s IDs
3. We have to help you setup the relationship from this record to other records, since this traditionally requires that you know the saleforce.com record to which you want a relationship.

We addressed the first two of these in our 7.0 API.  We introduced the new verb, upsert(), so that we had a verb that would just do the right thing (insert or update).  We also allowed you to designate certain fields as external ID fields.  These fields allow salesforce.com to hold the ID from the external system so that we can do the matching accurately and efficiently.    The upsert() method takes an addtional parameter over update, specifically the name of the external ID field to use for matching for the call.   

In our 8.0 API, we tackled the third.   Our 8.0 API added the concept of relationships to our API.   The changes to our API’s data model gave us a flexible way to express the relationship between objects using an external ID.  The new data model allows parent objects to be embedded in an object.   We use this capability to allow you to specify both the name of the parent object’s external ID field and to include the external system’s ID value.

If you have custom fields with the external ID property set on both Account (DUNS__c) and on User (EmployeeID__c), this is what it looks like to use upsert() via our AJAX toolkit:

One caveat:  When you nest a parent object, you can only set one external ID field on it.  In other words, you cannot use upsert to do a cascaded update to the parent.

This may look like a lot of code, but it does a lot!  We were able to do the complete operation with a single call to the API.  Without upsert, this would have required two additional calls to get salesforce.com IDs on Account and User and some client logic to choose between create() and update().  Fewer API calls also means fewer network round trips    The result is less code, less client logic, and much higher performance!