Apex DML Statements

Apex allow us to perform Data Manipulation Language (DML) operations to insert, update, merge, delete, and restore data in a database.

Following DML Statements are available

1. Insert : 

Insert DML operation adds one or more sObjects records to our organisation.

Syntax: insert sObject[ ]

2. Update :

Update DML operation modifies one or more existing sObject records in our organisation's data 

Syntax : update sObject[ ]

3. Upsert :

Upsert DML operation creates new sObject records and updates the existing sObject records within single statement, externalId can be used with upsert Statement.

Syntax : upsert sObject[ ]  extenal_Id

external_Id should be used to identify the record that already exist in organisation's data. 

NOTE : if custom field declared as externalId does not have unique attribute selected, the context user must have "View All" object level permission for the target object or the "View All Data" permission so that upsert does not accidently insert a duplicate record.

Upsert uses the sObject record's primary key (or the external ID, if specified) to determine whether it should create a new object record or update an existing one:

• If the key is not matched, then a new object record is created.
• If the key is matched once, then the existing object record is updated.
• If the key is matched multiple times, then an error is generated and the object record is neither inserted or updated.

4. Delete :

Delete DML statement deletes the one or more existing sObject records from organisation's data.

Syntax : delete sObject[ ] 

5. Undelete :

Undelete statement restore one or more sObject records from the organisation Recycle bin.

Syntax : undelete sObject[];

6. Merge :

Merge statement can merge upto 3 records of same sObject into one of the records, deleting others and re-parenting any related records.

Syntax : merge sObject sObject; 

The first parameter represents the master record into which the other records are to be merged. The second parameter represents the one or two other records that should be merged and then deleted. You can pass these other records into the merge statement as a single sObject record or ID, or as a list of two sObject records or IDs

merge sObject sObject[]

merge sObject ID

merge sObject ID[]

NOTE : According to Salesforce we can only merge Accounts, Contacts and leads

sObjects that cannot be used together in DML Options

DML operations on certain sObjects can’t be mixed with other sObjects in the same transaction. This is because some sObjects affect the user’s access to records in the organization. These types of sObjects must be inserted or updated in a different transaction to prevent operations from happening with incorrect access level permissions. 

Following are the objects which can not be used with other sObjects when performing DML operations in same transaction

1. FieldPermissions

2. Group 

3. GroupMember

4. Object Permission

5. PermissionSet

6. PermissionSetAssignment

7. QueueSObject

8. ObjectTerritory2AssignmentRule

9. ObjectTerritory2AssignmentRuleItem

10. RuleTerritory2Association

11. SetUpEntityAccess

12. Territory2

13. Territory2Model

14. UserTerritoryToAssociation

15. User

check this link for more information : http://vrcloud.blogspot.in/2013/12/error-mixeddmloperation-few-days-back.html

There are few Objects which do not support DML but can be accessible in queries

• AccountTerritoryAssignmentRule
• AccountTerritoryAssignmentRuleItem
• ApexComponent
• ApexPage
• BusinessHours
• BusinessProcess
• CategoryNode
• CurrencyType
• DatedConversionRate
• NetworkMember (allows update only)
• ProcessInstance
• Profile
• RecordType
• SelfServiceUser
• StaticResource
• UserAccountTeamMember
• UserTerritory
• WebLink

Refer : https://www.salesforce.com/us/developer/docs/apexcode/Content/apex_dml_non_dml_objects.htm

All these DML Statements comes with some Governor Limits 

Governor Limit : http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_gov_limits.htm

Total number of DML Statements :                                                                                    150

Total number of records processed as a result of DML statements, Approval.process, or database.emptyRecycleBin                                          10,000

If suppose I have insert 5 record of Account, I can do that individually

Account acc1 = new Account();

Account acc2 = new Account();

Account acc3 = new Account();

Account acc4 = new Account();

Account acc5 = new Account();

insert acc1;

insert acc2;

insert acc3;

insert acc4;

insert acc5;

In above scenerio our 5 DML statements is been utilized which will cause an problem if I have to insert many records, our DML statement limit will reached easily for this.

To handle above problem we can use collection for insert or other DML  statements

List<Account> accountList = new List<Account>();

accountList.add(new Account(Name = 'Account A'));

accountList.add(new Account(Name = 'Account B'));

accountList.add(new Account(Name = 'Account C'));

insert accountList;

In above scenerio our only 1 DML statement is utilized. But if you want to take it even further we can do this with collection of sObjects, sObject is a base object in Apex ie every object can be cast into sObject which will allow us to create one list of different Objects.

List<sObject> allObject = new List<sObject>();

allObject.add(new Account());

allObject.add(new Account());

allObject.add(new Contact());

allObject.add(new Contact());

allObject.add(new Custom_Obj__c());

allObject.add(new Custom_Obj__c());

insert allObject;

using above method we are now able to execute insert different record of different objects in single DML statement.

We have now learnt how to conserve our governor Limits.