Apex DML Statements Simplified

APEX DML (Database Manipulation Language) lets you do manipulation of Sobject data/records. It could be either insert of a new record or updating an existing record or deleting a record, etc.

Let’s see each DML operation one by one while playing with a contact record 🙂

Insert

Insert operation is used to insert the records in Salesforce org. It could be either a list of records or a single record.

Inserting a single contact :

Contact con = new Contact(LastName = 'Stark');
insert con;

Inserting a list of Contact :

List<Contact> contactList = new List<Contact>();
Contact con1 = new Contact(LastName = 'Stark');
Contact con2 = new Contact(LastName = 'Rogers');

contactList.add(con1);
contactList.add(con2);

insert contactList;

In the above code block if a single contact data failed to be inserted then none of the other contact data will be inserted into the database.

Partial Insert of Contacts :

Suppose you want that partial save should happen even though there was an error in one of the contact data then you can use Database.insert

Let’s see Database.insert with an example.

List<Contact> contactList = new List<Contact>();
Contact con1 = new Contact(LastName = 'Stark');
Contact con2 = new Contact();

contactList.add(con1);
contactList.add(con2);

Database.insert(contactList, false);

In the above block, for Contact con2 we haven’t given the value in the mandatory field LastName. If we have used a normal insert operation then we would have got an error while execution.

Datbase.insert(contactList, false) allows us to do a partial save of the records while skipping the failed one.

Database.insert returns a list of SaveResult which we can use to identify the success or error that occurred while inserting records.

List<Database.SaveResult> srList = Database.insert(contactList, false);

for (Database.SaveResult result : srList) {
   if (!result.isSuccess()) {
        for (Database.Error err : result.getErrors()) {
            System.debug(err.getMessage());
        }
   }
}

In the above code block, we are iterating SaveResult list to identify the error occurred while saving the records.

Update

Insert operation is used to insert the records in Salesforce org. It could be either a list of records or a single record.

Updating a single contact :

// Assuming a single contact record exist with lastName = 'Stark'
// If no record is found an error will be thrown - System.QueryException: List has no rows for assignment to SObject
// If multiple records are returned by the soql then below declaration will lead to an error - System.QueryException: List has more than 1 row for assignment to SObject
Contact conToBeUdpated = [Select id, LastName from Contact where LastName = 'Stark'];
conToBeUdpated.LastName = 'Stark';
update conToBeUdpated;

Updating a list of Contact :

List<Contact> contactList = new List<Contact>();        

for(Contact con : [SELECT LastName FROM Contact WHERE LastName = 'Starkkk']) {
    con.LastName = 'Stark';
    contactList.add(con);
}

if(! contactList.isEmpty()) {
    update contactList;
}

In the above code block, we are iterating over the SOQL list and adding the updated contact to the contactList. Once the loop ends contactList is updated.

Here also if one record is not able to update then not a single record will be updated in the list.

Partial Update a list of records :

To allow partial update of records you have to use Database.update

List<Contact> contactList = new List<Contact>();        

for(Contact con : [SELECT LastName FROM Contact WHERE LastName = 'Starkkk']) {
    con.LastName = 'Stark';
    contactList.add(con);
}

if(! contactList.isEmpty()) {
    List<Database.SaveResult> srList = Database.update(contactList, false);

    for (Database.SaveResult result : srList) {
        if (!result.isSuccess()) {
            for (Database.Error err : result.getErrors()) {
                System.debug(err.getMessage());
            }
        } 
    }
}

In the above block, we are using Database.update(contactList, false) to allow for partial update of contact records. If any error occurred for one of the records it will be skipped.

Also, Database.update returns the List of Database.SaveResult. We are using it similar to the insert operation to get the error messages for failed update records.

Upsert

Upsert is a combination of two operations that is ‘Update’ or ‘Insert’.

This is useful for scenarios when you have prepared a list and want that if there are no records present in the Salesforce org with the given value then do an insert operation otherwise do an update operation.

Upserting a single contact :

Contact con = new Contact(LastName = 'Stark', AnotherName__c = 'IronMan');
upsert con Contact.Fields.AnotherName__c;

In the above code block, along with the upsert statement we have specified Contact.Fields.AnotherName__c.

This field AnotherName__c is a custom field created in the org which is also declared as an external Id. It is mandatory for a field that will be used as an identifier in upsert operation to be set as an external Id.

if you don’t specify any field in the upsert call then by default Id is picked for checking the update or insert call. Let’s say we used the upsert call in the below manner then Id would have been picked by default to consider for an update or insert call.

Contact con = new Contact(LastName = 'Stark', AnotherName__c = 'IronMan');
upsert con;

Upserting a list of Contacts :

List<Contact> contactList = new List<Contact>();  
Contact con1 = new Contact(LastName = 'Stark', AnotherName__c = 'IronMan');
Contact con2 = new Contact(LastName = 'Rogers', AnotherName__c = 'CaptainAmerica');      

contactList.add(con1);
contactList.add(con2);

upsert contactList Contact.Fields.AnotherName__c;

In the above code block, we have created a list that is contactList which contains the contact data which will be upserted later.

Partial Upserting a list of Contacts :

You can use Database.upsert to allow for partial upsert of records.

List<Contact> contactList = new List<Contact>();  
Contact con1 = new Contact(LastName = 'Stark', AnotherName__c = 'IronMan');
Contact con2 = new Contact(LastName = 'Rogers', AnotherName__c = 'CaptainAmerica');        

contactList.add(con1);
contactList.add(con2);

if(!contactList.isEmpty()) {
    List<Database.UpsertResult> urList = Database.upsert(contactList, Contact.Fields.AnotherName__c, false);
    for (Database.UpsertResult result : urList) {
       if (!result.isSuccess()) {
            for (Database.Error err : result.getErrors()) {
                System.debug(err.getMessage());
            }
       } 
    }
}

In the above block, we are using Database.upsert(contactList, Contact.Fields.AnotherName__c, false) to allow for partial upsert of contact records. If any error occurred for one of the records it will be skipped.

Also, Database.upsert return List of Database.UpsertResult. We are using it similar to the insert operation to get the error messages for failed upsert records.

Delete

Delete is used to delete records from the Salesforce org.

Deleting a single contact :

// Assuming a single contact record exist with lastName = 'Stark'
// If no record is found an error will be thrown - System.QueryException: List has no rows for assignment to SObject
// If multiple records are returned by the soql then below declaration will lead to an error - System.QueryException: List has more than 1 row for assignment to SObject

Contact conToBeUdpated = [Select id, LastName from Contact where lastName = 'Stark'];
delete conToBeUdpated;

Deleting a list of Contacts :

List<Contact> contactList = new List<Contact>();        

for(Contact con : [SELECT LastName FROM Contact WHERE LastName = 'Stark' LIMIT 1] ) {
    contactList.add(con);
}

if(! contactList.isEmpty()) {
    delete contactList;
}

In the above code block, we are iterating over the SOQL list and adding the contact to be deleted in the contactList. Once the loop ends contactList is deleted.

Here also if one record is not able to be deleted then all the records will not be deleted from the list.

Partial Deleting a list of Contacts :

You can use Database.delete to allow for partial upsert of records.

List<Contact> contactList = new List<Contact>();        

for(Contact con : [SELECT LastName FROM Contact WHERE LastName = 'Stark' LIMIT 1] ) {
    contactList.add(con);
}

if(! contactList.isEmpty()) {
    List<Database.DeleteResult> srList = Database.delete(contactList, false);
    for (Database.DeleteResult result : srList) {
       if (!result.isSuccess()) {
            for (Database.Error err : result.getErrors()) {
                 System.debug(err.getMessage());
            }
       } 
    }
}

In the above block, we are using Database.delete(contactList, false) to allow for partial delete of contact records. If any error occurred for one of the records it will be skipped.

Database.delete return List of Database.DeleteResults. We are using it similar to the insert operation to get the error messages for failed delete records.

Merge

The merge statements let you merge up to three records of the same sObject type into one of the records, deleting the others, and re-parenting any related records.

They are ideal for cleaning duplicate records in the Salesforce org.

Merge is only allowed for leads, contacts, cases, and accounts

Merging a single contact :

Contact masterContact = [SELECT Id, Name FROM Contact WHERE Name = 'Tony Stark' LIMIT 1];
Contact mergeContact = [SELECT Id, Name FROM Contact WHERE Name = 'Iron Man' LIMIT 1];
merge masterContact mergeContact;

In the above code block, we declare the mergeContact which will be merged into masterContact. Any related references for mergeContact will be also merged into the masterContact.

Merging a list of Contacts :

Contact masterContact = [SELECT Id, LastName FROM Contact WHERE LastName = 'Tony Stark' LIMIT 1];
Contact mergeContact1 = [SELECT Id, LastName FROM Contact WHERE Name = 'Iron Man' LIMIT 1];
Contact mergeContact2 = [SELECT Id, LastName FROM Contact WHERE Name = 'Awesome Avenger' LIMIT 1];

List<Contact> contactToMergeList = new List<Contact>();
contactToMergeList.add(mergeContact1);
contactToMergeList.add(mergeContact2);

merge masterContact contactToMergeList;

In the above code block, we are adding the contact to be merged in the contactToMergeList.

Here also if one record is not able to merge then the other records will not be merged in the list.

Partial Merging a list of Contacts :

You can use Database.merge to allow for partial merge of records.

Contact masterContact = [SELECT Id, LastName FROM Contact WHERE LastName = 'Tony Stark' LIMIT 1];
Contact mergeContact1 = [SELECT Id, LastName FROM Contact WHERE Name = 'Iron Man' LIMIT 1];
Contact mergeContact2 = [SELECT Id, LastName FROM Contact WHERE Name = 'Awesome Avenger' LIMIT 1];

List<Contact> contactToMergeList = new List<Contact>();
contactToMergeList.add(mergeContact1);
contactToMergeList.add(mergeContact2);

Database.MergeResult[] mrList = Database.merge(masterContact, contactToMergeList, false);
for (Database.MergeResult result : mrList) {
   if (!result.isSuccess()) {
        for (Database.Error err : result.getErrors()) {
            System.debug(err.getMessage());
        }
   } 
}

In the above block we are using Database.merge(masterContact, contactToMergeList, false) to allow for partial merge of contact records. If any error occurred for one of the records it will be skipped.

Database.merge return List of Database.MergeResult. We are using it similar to the insert operation to get the error messages for failed merge records.

Undelete

Undelete lets you restore or recover records from your Salesforce org recycle bin.

UnDeleting a single contact :

// Assuming a single contact record exist with lastName = 'Stark'
// If no record is found an error will be thrown - System.QueryException: List has no rows for assignment to SObject
// If multiple records are returned by the soql then below declaration will lead to an error - System.QueryException: List has more than 1 row for assignment to SObject

Contact conToBeUdpated = [SELECT Id, LastName FROM Contact WHERE LastName = 'Stark' ALL ROWS];
undelete conToBeUdpated;

ALL ROWS keyword queries all the rows which include the records deleted and are present in recycle bin.

UnDeleting a list of Contacts :

List<Contact> contactList = new List<Contact>();        

for(Contact con : [SELECT Id, LastName FROM Contact WHERE LastName = 'Stark' ALL ROWS] ) {
    contactList.add(con);
}

if(! contactList.isEmpty()) {
    undelete contactList;
}

In the above code block, we are iterating over the SOQL list and adding the contact to be undeleted in the contactList. Once the loop ends contactList is undeleted.

Here also if one record is not able to be undeleted then all the records will not be undeleted in the list.

Partial Deleting a list of Contacts :

You can use Database.undelete to allow for partial upsert of records.

List<Contact> contactList = new List<Contact>();        

for(Contact con : [SELECT Id, LastName FROM Contact WHERE LastName = 'Stark' ALL ROWS] ) {
    contactList.add(con);
}

if(! contactList.isEmpty()) {
    undelete contactList;
}

List<Database.UndeleteResult> udList = Database.Undelete(contactList, false);

for (Database.UndeleteResult result : udList) {
    if (!result.isSuccess()) {
        for (Database.Error err : result.getErrors()) {
            System.debug(err.getMessage());
        }
    }
}

In the above block we are using Database.undelete(contactList, false) to allow for partial undelete of contact records. If any error occurred for one of the records it will be skipped.

Database.undelete return List of Database.UndeleteResult. We are using it similar to the insert operation to get the error messages for failed undelete records.

Rollback

Rollback gives you the ability to roll back the transaction if any caught exception occurs or on certain conditions.

Savepoint sp =  Database.setSavePoint();

try {
    Contact con1 = new Contact(lastName = 'Stark');
    insert con1;

    Contact con2 = new Contact();
    insert con2;
} catch(Exception e) {
    Database.rollback(sp);
    System.debug(e.getMessage());
}

In the above code block, we have declared Savepoint sp.

Database.rollback(sp) reverts or rolls back the database state to a point where Savepoint was declared.

What will happen if rollback is not used in the above code.

try {
    Contact con1 = new Contact(lastName = 'Stark');
    insert con1;

    Contact con2 = new Contact();
    insert con2;
} catch(Exception e) {
    System.debug(e.getMessage());
}

While inserting con2 in the try block an exception will occur as it didn’t have the value in the mandatory field. Code execution will move to the catch block and the transaction will be complete due to exception handling.

As the exception was handled and the transaction was completed so insert operation for con1 was not reverted.

If there were no try-catch block present, con1 would have not committed as the transaction would end up in an uncaught exception while inserting con2.

Thank you for visiting SalesforceBlue.com
If you have any queries feel free to write down a comment below 🙂