Database Manipulation operations – insert, update, upsert, delete, undelete & merge

Use Data Manipulation Language (DML) Operations to insert, update, delete, and restore data in a database. Below are the different database manipulation languages in Apex.

insert

Insert 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;

Apex DML Statement, Such as

  •   Insert sObject();

Apex DML database methods, such as

Database.severesult() result=Database Insert(sobject());

The following example that inserts a new invoice statement by calling insert and if we execute in the ‘Developer console” that creates a record into the database.

Invoice_Statement__c Inv=new invoice_statement__c(Description__c='Salesforcetutorial.com');
Insert Inv is used for inserting the invoice using DML

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.

         Update SObject;

        Update Subject[ ];

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) {
	// handle exception if any
}

upsert

upsert 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) {
	// handle exception if any
}

delete

The delete DML operation deletes one or more existing SObject records, such as individual accounts or contacts, from organization’s data. Delete keyword is used to delete the records.

The DML (Delete) statement syntax is Delete SObject;

Delete SObject[ ];

Example

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

try {
	delete delAccts;
} catch (DmlException e) {
	// Process exception here
}
  • Deleting a record places it in the recycle bin from where we can restore it. Records in the Recycle bin are temporarily stored for 15 days before they are permanently deleted.
  •  To restore just use the undelete DML statement .Notice that we used the ALL ROWS keywords in the SOQL query to be able to retrieve the delete record

undelete

This keyword is used to restore the records from the recycle bin.

Example

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

try {
	undelete accts;
} catch (DmlException e) {
	// handle the exception
}

Merge

This keyword merges up to three records of the same type into one of the records, deleting the others, and reparenting any related records.

Example

List<Account> accList = new List<Account>{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) {
	// handle the exception
}