Apex DML Best Practices: Insert vs Update vs upsert
Learn the Apex DML operations insert, update, delete, and upsert, when to use each one, and how to avoid the partial-success mistakes that fail at runtime.
TL;DR
Apex has four DML operations you will use every day: insert adds new records, update changes records that already have an Id, delete removes them, and upsert does both at once by matching on an external ID field. The mistake that catches most beginners is update on a record that has no Id yet, because Apex has no way to tell which row you meant. The second is assuming one DML statement is all-or-nothing, when a single bad row can roll back the whole batch. Pick the operation that matches what you want, give every record an Id before you update it, and check each row's result when a batch matters.
What DML Actually Is
DML stands for Data Manipulation Language. It is the set of Apex verbs that change data in your org: insert, update, delete, upsert, and undelete. Everything else in Apex reads data with SOQL, and everything else in Apex does not touch records at all.
Here are the first three operations, which cover most of what you write:
// Insert a single new Account
Account account = new Account(
Name = 'Acme Corporation',
Industry = 'Technology',
Website = 'https://acme.example.com'
);
insert account;
System.debug('New Account Id: ' + account.Id);Notice what insert gives you back. After the statement runs, account.Id is populated with the new record's Id. That Id does not exist before the insert. This matters for the next section.
Updating is the mirror image. You change the field values on a record, then hand it to update:
// Update an existing Account
Account account = [SELECT Id, Name, Phone FROM Account LIMIT 1];
account.Phone = '(555) 987-6543';
update account;delete takes records and removes them. undelete brings back records you deleted in the same transaction, and it only works on soft-deleted records.
The Mistake That Costs Everyone an Hour
Here is the bug I see most often in code review. A developer writes a new record, changes a field on it, and then tries to update it:
// This throws: Id not specified in an update call
Account account = new Account(Name = 'Acme Corporation');
account.Phone = '(555) 987-6543';
update account;The error message is MISSING_ARGUMENT, Id not specified in an update call. It is not a subtle failure. The reason is simple: update works by Id, and a record you just built with new has no Id yet. You have not saved it, so there is nothing for the platform to look up.
There are two correct ways to fix this. The first is to insert the record first, then update it in a second statement:
Account account = new Account(Name = 'Acme Corporation');
insert account;
// Now the Id exists, so update works
account.Phone = '(555) 987-6543';
update account;The second is to set the field before the insert, and insert once. If the record does not exist yet, there is usually no reason to update it. Put all the field values on the new statement and save it once.
The rule to remember: update needs an Id that already exists in the org. If you do not have one, you are inserting, not updating.
Upsert: Insert or Update in One Statement
upsert is the operation for when you do not know yet whether a record exists. It takes a list of records and a field to match on. If a record with that field value already exists, upsert updates it. If not, upsert inserts it.
The key has to be a field the platform can match on. The compiler's rule is that it must be an external ID field, either a custom one or a standard field the platform indexes. On Contact, the standard Email field is one that works:
// Insert or update contacts, matching on Email
List<Contact> contacts = new List<Contact>{
new Contact(FirstName = 'John', LastName = 'Doe', Email = 'john@example.com'),
new Contact(FirstName = 'Jane', LastName = 'Smith', Email = 'jane@example.com')
};
upsert contacts Email;
System.debug('Upserted ' + contacts.size() + ' contacts');After the statement runs, each record in the list has an Id. A record that matched an existing row keeps that existing Id, and a record that did not match gets a brand new one. You can read the Ids straight off the list you passed in.
Not every field qualifies. An ordinary field like AccountNumber or LastName is rejected at compile time, before your code runs, with Invalid field for upsert, must be an External Id custom or standard indexed field. If the field you want to match on is not already usable, you need an administrator to mark it as an external ID, or add a dedicated external ID field to the object.
Three rules about upsert keys are worth knowing before you write one. First, every record in the list must have the key field populated. A record with a missing key fails the whole statement with MISSING_ARGUMENT, Email not specified. Second, an upsert does not skip a record whose key is null, it fails. Third, the key has to identify exactly one record. If two existing rows already share that value, the upsert stops with DUPLICATE_EXTERNAL_ID, because the platform cannot tell which one you meant.
If you want the per-row results instead of an all-or-nothing statement, use the Database class. It takes the field as a Schema.SObjectField, and it returns its own result type:
List<Contact> contacts = new List<Contact>{
new Contact(FirstName = 'John', LastName = 'Doe', Email = 'john@example.com')
};
List<Database.UpsertResult> results = Database.upsert(
contacts,
Schema.SObjectType.Contact.fields.getMap().get('Email'),
false
);
System.debug('Upsert results: ' + results.size());All or Nothing: The Default You Should Know About
By default, a DML statement is all or nothing. If any record in the batch fails, the whole statement rolls back. Nothing is saved. You get one exception, and the first error message names the row that broke.
That default is safe, and it is right for most code. But it is not always what you want. Consider an import where one bad row should not cancel the other 999 good rows. For that, the Database class has an allOrNone flag. Pass false and each row succeeds or fails on its own:
List<Contact> contacts = new List<Contact>{
new Contact(LastName = 'Valid', Email = 'valid@example.com'),
new Contact(FirstName = 'MissingLastName')
};
List<Database.SaveResult> results = Database.insert(contacts, false);
for (Integer i = 0; i < results.size(); i++) {
if (!results[i].isSuccess()) {
Database.Error err = results[i].getErrors()[0];
System.debug('Row ' + i + ' failed: ' + err.getStatusCode() + ' - ' + err.getMessage());
}
}The results list lines up with your input list by index, so results[0] describes contacts[0]. A failed row comes back with a status code like REQUIRED_FIELD_MISSING and a message naming the missing fields.
Each Database method returns the result type that fits it. Database.insert and Database.update give back List<Database.SaveResult>. Database.delete gives back List<Database.DeleteResult>. Database.upsert gives back List<Database.UpsertResult>. Declaring the wrong type is a compile error, not a runtime surprise, so the compiler will catch a mismatch for you.
Merging Two Records Into One
Sometimes the job is not to update a record but to fold one record into another. That is merge. You name two records: the master, which survives, and the duplicate, which is deleted:
Account master = new Account(Name = 'Acme Corporation', Industry = 'Technology');
Account duplicateRecord = new Account(Name = 'Acme Corporation', Phone = '555-0100');
insert master;
insert duplicateRecord;
merge master duplicateRecord;The result surprises people. The master record survives with its own Id, and the duplicate record is deleted. But the master does not inherit field values from the duplicate. If you set Industry on the master and Phone on the duplicate, the merged master keeps Industry and its Phone is empty. If you want the duplicate's values, copy them across before you merge.
Rolling Back Part of a Transaction
Sometimes a transaction does something you want to undo before it ends. Savepoints let you mark a point and roll back to it:
Savepoint sp = Database.setSavepoint();
Account account = [SELECT Id, Name FROM Account LIMIT 1];
account.Name = 'Rolled Back Name';
update account;
Database.rollback(sp);After the rollback, the update is gone but the rest of the transaction continues. This is the tool for "try it, and if it does not work, undo it" logic inside a single transaction.
The API here has a sharp edge. Database.setSavepoint() returns a Savepoint value you keep in a variable. There is no Database.savepoint('name') statement form, and Database.setSavepoint('name') does not exist either. The no-argument call is the one that compiles.
Choosing the Right Operation
Here is the decision in one list:
- You have new records and no Ids —
insert. - You have records with Ids and want to change them —
update. - You want to remove them —
delete. - You do not know yet whether they exist, and you have an external ID —
upsert. - One bad row must not cancel the good ones — use the
Databaseclass withallOrNoneset tofalse. - You want to fold one record into another —
merge. - You want to undo part of the transaction —
Database.setSavepoint()andDatabase.rollback().
One last limit worth keeping in your head. A single transaction can touch at most 10,000 records with DML. An insert of 10,001 records fails with System.LimitException: Too many DML rows. When you are moving more than 10,000 records, you need a batch or a queueable, and that is a different topic.
Where To Practice Next
Put the ideas to work on the platform. The upsert contacts by email challenge asks you to insert or update a list of contacts keyed on Email, which is exactly the operation above. If you want the fundamentals first, Apex Fundamentals walks through the language step by step, and the DML and records lesson covers the record model behind these verbs.
For more on the operation that catches people out most, Salesforce Governor Limits Explained covers what happens when you put DML in a loop. And if you want to see the other side of the same coin, Salesforce APIs Explained compares DML against the REST and Metadata APIs so you know when to leave Apex for an API call.
About Warren Walters
Salesforce MVP and transformative mentor with 8+ years in the Salesforce realm. Founder of Lightning Challenge, dedicated to nurturing the next generation of Salesforce talent through hands-on practice and real-world coding challenges.
Visit Profile →