What does upsert do in Apex?

Upsert is a DML operation that either inserts or updates a record in a single statement. Salesforce checks whether a matching record already exists using a field you specify — typically an External ID field. If a match is found, the record is updated. If no match is found, a new record is inserted. This eliminates the need to query first to determine whether to insert or update — a common pattern in data integrations.

What is an External ID field in Salesforce and why is it required for upsert?

An External ID field is a custom field that has been designated as External ID in the field definition in Salesforce Setup. When you run an upsert with a specific field — upsert records ExternalId__c — Salesforce uses that field's value to find existing records. The field must be marked as External ID for Salesforce to use it for matching. Without this designation, Salesforce falls back to matching on the record's Salesforce Id field. The field should also be marked as Unique to prevent multiple-match errors.

What is the difference between upsert and Database.upsert() in Apex?

The DML form — upsert records ExternalId__c — throws a DmlException if any record fails, rolling back all records in the call (allOrNone = true by default). Database.upsert(records, ExternalId__c.class, false) allows partial success: records that fail do not cause the entire operation to roll back. Database.upsert also returns a list of UpsertResult objects that tell you for each record whether it succeeded, whether it was inserted or updated (isCreated()), and what errors occurred.

What does merge do in Apex and what happens to related records?

Merge consolidates two or three duplicate records into one master record. The master record survives. All related records — Contacts, Opportunities, Cases, Activities, and child records from any related list — are automatically re-parented to the master. The duplicate records are permanently deleted and cannot be recovered from the recycle bin. Syntax: merge masterRecord duplicates, where duplicates is a single sObject or a list of up to two sObjects.

When should I use upsert versus merge in Salesforce Apex?

Use upsert when syncing records from an external system where you cannot guarantee whether records already exist in Salesforce — data integrations, nightly sync jobs, data migration loads where idempotency matters. Use merge when you have already identified specific duplicate records that need to be consolidated — deduplication workflows, post-migration cleanup, or programmatic duplicate resolution triggered by a data quality check or Duplicate Rule match.

What governor limits apply to upsert and merge in Apex?

Upsert: each upsert call counts as one DML statement against the 150-statement limit, regardless of whether records are inserted or updated. Records count against the 10,000-row DML limit. The limits are the same as for standard insert or update calls. Merge: each merge call counts as one DML statement. There is no additional governor limit charge for re-parenting related records during a merge. Database.merge does not support allOrNone = false — failures always throw a DmlException.

Upsert and Merge in Apex: DML Operations for Integrations and Deduplication

Most Salesforce developers learn insert, update, and delete in week one and use them constantly. Upsert and merge appear later — in real integration work, data migrations, and deduplication workflows — and they solve problems that the standard DML operations cannot handle on their own. This session covers both: what they do, when to use them, how to set up External ID fields for upsert, the Database.upsert versus merge syntax forms, and error handling patterns.

Upsert: Insert or Update Without a Pre-Query

The problem upsert solves is common in data integrations: an external system sends you records and you don't know in advance whether each one already exists in Salesforce. Without upsert, you run a query to find which IDs already exist, split the incoming list into two groups, run an insert for new records, and run an update for existing ones. That's three operations with an extra SOQL query and branching logic to maintain.

Upsert collapses this into one statement:

upsert records ExternalId__c;

Salesforce checks each incoming record's External ID value against existing records in the org. Match found: it updates. No match: it inserts. One DML statement handles both cases.

External ID Fields: The Pre-Requisite

For upsert to match on a custom field, that field must be designated as an External ID in Salesforce Setup. You cannot use upsert matching on an arbitrary field — even if the values in that field are unique.

To set up an External ID field: in Setup, go to the object's Fields section, create a new field (Text, Number, Email, or URL are the compatible types), check the External ID checkbox in the field definition, and optionally check Unique. The Unique flag prevents multiple records from having the same External ID value, which is important because a upsert that matches multiple records on the same External ID will throw a DmlException.

Once the field is created and designated as External ID, you reference it in the upsert statement by its API name:

// Upsert a list of accounts using the External ID field
List<Account> accounts = new List<Account>();
// ... populate accounts from external system ...
upsert accounts ExternalSystemId__c;

If you run upsert without specifying a field — just upsert records — Salesforce falls back to matching on the record's Salesforce Id field. This only works if your incoming records already have the Salesforce Id populated, which is not common in external-to-Salesforce sync scenarios.

UpsertResult: What Database.upsert Returns

The DML form of upsert — upsert records ExternalId__c — throws a DmlException if any record fails, rolling back all records in the operation. To allow partial success, use Database.upsert:

Database.UpsertResult[] results = Database.upsert(accounts, Account.ExternalSystemId__c, false);

for (Database.UpsertResult r : results) {
    if (r.isSuccess()) {
        if (r.isCreated()) {
            System.debug('Inserted: ' + r.getId());
        } else {
            System.debug('Updated: ' + r.getId());
        }
    } else {
        for (Database.Error err : r.getErrors()) {
            System.debug('Error: ' + err.getMessage() + ' Fields: ' + err.getFields());
        }
    }
}

The third parameter to Database.upsert is allOrNone. Setting it to false allows partial success — records that succeed are committed, records that fail are not, and no exception is thrown. The UpsertResult list contains one entry per record in the same order as the input list.

UpsertResult has one method that SaveResult (from insert and update) does not: isCreated(). This tells you whether the record was inserted (true) or updated (false) in this call. This matters in integration logging where you want to track how many records were new versus how many were updates.

Governor Limits for Upsert

Each upsert call counts as one DML statement against the 150-statement limit, regardless of how many records in the list are inserted versus updated. Records count against the 10,000-row DML row limit. The limits are the same as for a standard insert or update — upsert does not consume additional governor limit capacity compared to the two separate calls it replaces.

Merge: Deduplication Without Data Loss

Merge consolidates duplicate records. You specify a master record that survives and up to two duplicate records that are deleted. All related records — any child object that has a lookup or master-detail relationship to the merged object — are re-parented to the master automatically. The duplicate records are permanently deleted and are not accessible through the recycle bin.

Syntax:

// Merge two duplicate accounts into a master
Account master = [SELECT Id FROM Account WHERE Name = 'Acme Corp' LIMIT 1];
Account duplicate = [SELECT Id FROM Account WHERE Name = 'ACME Corp' LIMIT 1];

merge master duplicate;

// Merge up to two duplicates at once
merge master new List<Account>{duplicate1, duplicate2};

You cannot merge more than three records in a single merge statement — one master and up to two duplicates. If your deduplication job has identified more than two duplicates for the same master, you need multiple sequential merge calls.

What Happens During Merge

The master record survives with its current field values. Field values from the duplicate records are not automatically carried over to the master — the master's values take precedence. Related records (child objects in related lists) are re-parented to the master. The duplicate sObjects are deleted.

Field-level security applies during merge. If the running user cannot see a field on the duplicate record due to FLS restrictions, that field's value will not be considered for carryover to the master even if the master's version of that field is blank. Run merge as a user with sufficient access to all relevant fields, or use without sharing in the class if your security model supports it.

Permissions Required for Merge

The running user must have Edit access on the master record and Delete access on the duplicate records. If the user lacks delete access on a duplicate, the merge throws a DmlException. In production code that runs merge programmatically — for example, in a batch job — ensure the running user profile or permission set includes delete on the relevant object.

Database.merge

The alternative syntax passes IDs rather than sObjects:

Database.MergeResult result = Database.merge(masterAccount, duplicateIds);

if (result.isSuccess()) {
    System.debug('Merged. Re-parented IDs: ' + result.getMergedRecordIds());
} else {
    System.debug('Failed: ' + result.getErrors()[0].getMessage());
}

Note that unlike Database.upsert, Database.merge does not support allOrNone = false. A failure in merge throws a DmlException regardless of which syntax form you use. There is no partial success mode for merge — always wrap merge in a try/catch block.

When to Use Upsert

Upsert is the standard pattern for data integrations from external systems where records arrive without a guarantee of whether they already exist in Salesforce. It is also useful in data migration scripts where idempotency matters — running the same load twice should not create duplicate records. Any sync job where the insert-versus-update determination cannot be made without a pre-query is a candidate for upsert.

When to Use Merge

Merge is for consolidating records that have already been identified as duplicates. It is not a search tool — it assumes you have done the work of finding which records are duplicates before you call merge. Common uses: deduplication workflows where a Duplicate Rule or a data quality report has identified specific Account or Contact duplicates, post-migration cleanup where the migration process introduced duplicates, and batch jobs that process a queue of known duplicate pairs.

Common Patterns

The standard upsert integration pattern: receive JSON from an external API callout, deserialise the JSON to a list of sObjects, set the External ID field on each record from the incoming data, run Database.upsert with allOrNone = false, process UpsertResult to log inserts versus updates and handle individual failures.

The standard merge pattern: a scheduled batch class queries pairs of duplicate records identified by a data quality check, processes them in execute() with a try/catch around each merge call, logs MergeResult including the re-parented record IDs, and sends a summary report to an admin on finish().

In both patterns, the setup work is the pre-requisite: External ID field creation and designation for upsert, and identifying duplicate record IDs before the batch runs for merge.