Database Manipulation Operations(DMLs) – Insert, Update, Upsert, Delete, Undelete And Merge

Database Methods

Database methods are an alternative to the standard DML operations.
The syntax for these is as below:

database.<operation name/>(<list of records/>,<all or none boolean/>);

These allow the ability to support the partial save of a collection of records, that is, if we use the update statement to update a list of 100 Account records and any one for the Account fails the update, all the 100 Accounts will not be saved. But if we use the database.update method to update the same list and pass the allOrNone variable as false, the failure of updation of one account in the list will not fail all the other 99 Account records.
Below are the examples for using these in apex :

1. Database.insert : The database class alternative for the insert statement.
   Example :

Lead l = new Lead(Company='ABC',LastName='Rahul');
database.insert(l);

2. Database.update : The database class method for the update operation.
   Example :

Account myAcct = [SELECT Id, Name, BillingCity FROM Account WHERE Name = 'MyAcc1' LIMIT 1];
myAcct.BillingCity = 'Melbourne';

database.update(myAcct);

3. . Database.upsert : The database class method for the upsert operation.
   Example :

Account[] acctsList = [SELECT Id, Name, BillingCity FROM Account WHERE BillingCity = 'Bombay'];
for (Account a : acctsList) {
    a.BillingCity = 'Mumbai';
}
Account newAcct = new Account(Name = 'Ramco', BillingCity = 'Hyderabad');
acctsList.add(newAcct);

database.upsert(newAcct,false);

4. Database.delete : The database class method for the delete operation.
   Example :

Account[] delAccts = [SELECT Id, Name FROM Account WHERE Name = 'domnos'];

database.delete(delAccts,false);

5. Database.undelete : The database class method for the undelete operation.
   Example :

Account[] accts = [SELECT Id, Name FROM Account WHERE Name = 'myAcc' ALL ROWS];

database.undelete(accts,false);

6. Database.merge : The database class method for the merge operation.
   Example :

Account masterAcct = [SELECT Id, Name FROM Account WHERE Name = 'Myacc1' LIMIT 1];
Account mergeAcct = [SELECT Id, Name FROM Account WHERE Name = 'MyAcc2' LIMIT 1];

database.merge(masterAcct,mergeAcct.Id);

Difference between DML Statements and Database Methods

1. insert: This keyword is used to insert one or more records. See the below example to understand how can we use this in apex code.
   Example:

Lead l = new Lead(Company=’ABC’,LastName=’Rahul’);
insert l;

2. update: This keyword is used to update/modifications to the existing records. See the below example to understand how can we use this in apex code.

   Example:

Account a = new Account(Name=’MyAcc1′);
insert a;
Account myAcct = [SELECT Id, Name, BillingCity FROM Account WHERE Name = ‘MyAcc1’ LIMIT 1];
myAcct.BillingCity = ‘Melbourne’;
try {
    update myAcct;
} catch (DmlException e) { }

3. upsert: This keyword is used to creates/insert new records and updates existing records. See the below example to understand how can we use this in apex code.

   Example:

Account[] acctsList = [SELECT Id, Name, BillingCity FROM Account WHERE BillingCity = ‘Bombay’];
for (Account a : acctsList) {
    a.BillingCity = ‘Mumbai’;
}
Account newAcct = new Account(Name = ‘Ramco’, BillingCity = ‘Hyderabad’);
acctsList.add(newAcct);
try {
    upsert acctsList;
}
catch (DmlException e) { }

4. delete: This key word is used to delete the records.

   Example:

Account[] delAccts = [SELECT Id, Name FROM Account WHERE Name = ‘domnos’];
try {
    delete delAccts;
} catch (DmlException e) {
    // Process exception here
}

5. undelete: This keyword is used to restore the records from the recyclebin.

   Example:

Account[] accts = [SELECT Id, Name FROM Account WHERE Name = ‘myAcc’ ALL ROWS];
try {
    undelete accts;
}
catch (DmlException e) {
    // Process exception here
}

6. Merge: This keyword merges up to three records of the same type into one of the records, deleting the others, and re-parenting any related records.
   Example:

List accList = new List{new Account(Name=’Myacc1′),new Account(Name=’MyAcc2′)};
insert accList;
Account masterAcct = [SELECT Id, Name FROM Account WHERE Name = ‘Myacc1’ LIMIT 1];
Account mergeAcct = [SELECT Id, Name FROM Account WHERE Name = ‘MyAcc2’ LIMIT 1];
try {
    merge masterAcct mergeAcct;
}
catch (DmlException e) { }