Straight answer: upsert matches on one field you nominate, and everything that goes wrong with it comes from that field being a worse key than you thought. Blank values, values that repeat, values that differ only in case, values a user can edit. Fix the field and upsert becomes the most reliable operation in the platform: run it twice and nothing duplicates.
What upsert actually does
You give it a field and a value. It looks for one record where that field matches. One match, it updates. No match, it creates. More than one match, it fails that row and tells you the match was ambiguous.
That is the whole behaviour, and the third case is the one to design against, because it is the only one you cannot fix from the caller. The API exposes this directly through the sObject Rows by External ID resource, and sObject Collections upserts up to 200 records against an external ID in one call. In Apex it is the upsert statement with the field named as the second argument.
The five things that make an external ID a bad key
It is empty on some records. Blank matches nothing, so those rows insert — every single time the job runs. This is the classic duplicate factory: a nightly upsert that has been creating a fresh copy of the same 300 records for a year, because their key never got populated.
Users can type in it. If the field is on a page layout and editable, someone will change it, and the next run inserts a new record because the old one no longer matches. Make it read-only for everyone except the integration.
It is not unique. External ID and Unique are two separate settings, and marking a field as an external ID does not stop two records sharing a value. Without Unique the platform will let the duplicate exist, and then upsert fails on ambiguity — a failure caused months earlier by a load nobody remembers.
Case. A key that comes in as abc-123 in one feed and ABC-123 in another either matches or does not depending on how the field was configured, and this is the failure that survives testing, because test data is usually uniform. Normalise case in the source before it reaches Salesforce, and pick one convention on purpose.
It carries meaning. A key built out of region plus year plus a counter changes when a record moves region, and then it identifies a different record than it used to. A key’s only job is to be stable. If it is also a business attribute, it will eventually be edited for business reasons.
Relate records without querying for the parent
The part of upsert most teams under-use: you can set a relationship by external ID without knowing the parent’s Salesforce Id.
Instead of querying the Account, taking the Id and putting it on the Contact, you populate the relationship using the parent’s external ID value, and the platform resolves it during the save. The Apex documentation covers relating records by external ID, and the APIs support the same idea.
Two things follow. Your integration stops needing a query per parent, which matters against the 100-query limit. And a load file becomes self-describing: source keys all the way down, no mapping step, no exported Id columns, no VLOOKUP.
Designing the key: what I would do
One external ID field per object, named for its source system — ERP_Id__c, not External_Id__c. When a second system arrives, and it will, you want a second clearly-named field rather than an argument about who owns the generic one.
Set it up as External ID and Unique, and make it read-only on every layout. If the source key can be blank, generate a deterministic value instead of leaving it empty: a hash of whatever does identify the record beats a null, because a null matches nothing and inserts forever.
And keep the field out of formulas and business logic. The moment reporting depends on its shape, you have lost the ability to change it.
Errors you will see, and what each one means
- Duplicate external id specified — two rows in the same request carry the same key. Deduplicate before sending, because the platform will not choose for you.
- Duplicate value found on a Unique field. The value already exists on another record. Usually a key collision from a source system that reuses numbers after deletion.
- Ambiguous match — two existing records share the key, so upsert cannot pick. The data problem is older than the run that reported it. Fix the duplicates first.
- Field is not an external ID — the field was not marked, or you are pointing at the wrong API name. Cheap to fix, easy to miss in a sandbox that was configured by hand.
Test the second run, not the first
An upsert integration that works once tells you almost nothing. The test that matters is running the same payload again and confirming the record count did not move.
Make that a standing check for every integration that writes: run, count, run again, count. It takes a minute and it catches the blank-key problem, the editable-field problem and the case problem in one go. All three of which look perfectly healthy on a first run.
Frequently asked
Can I upsert on the Salesforce Id? That is just an update. Upsert exists for the case where you do not know the Id, which is every integration with an outside system.
Does upsert count as one DML statement? Yes, one statement for the batch, and the rows count toward the DML row limit. It is not cheaper than insert plus update. It is safer.
What happens to fields I do not send? They are left alone on an update. Sending a field with an empty value is different from not sending it: the first clears the field, the second does not, and integrations regularly wipe data by not knowing which one their tool does.
Can I use a formula field as the external ID? No. It has to be a real field the platform can index and match on. If your key is derived, write it into a plain field with automation and index that.
