Salesforce
Salesforce Object Query Language (SOQL)
Copyright Intellipaat. All rights reserved.
Agenda
SOQL Comparison
01 Operators 02 SOQL Vs. SQL 03 SOQL Vs. SQL
DML
04 Statements 05 SaveResult Class 06 Transaction Control
07 [Link]
Copyright Intellipaat. All rights reserved.
Let us understand SOQL in depth
Copyright Intellipaat. All rights reserved.
SOQL Comparison Operators
Operator Common name
= Equals
!= Not equals
< Less than
<= Less than or equal
> Greater than
>= Greater than or equal
IN In
NOT IN Not in (Where Clause)
INCLUDES EXCLUDES Applies to multi-select picklists
LIKE Like
Copyright Intellipaat. All rights reserved.
Confused between SOQL and SQL?
Copyright Intellipaat. All rights reserved.
SOQL Vs. SQL
▪ SOQL is used only to perform queries with the SELECT statement. SOQL has no equivalent INSERT, UPDATE, and DELETE statements
▪ In the Salesforce world, data manipulation is handled using a set of methods known as DML (Data Manipulation Language)
▪ One big difference you’ll notice right away is that SOQL does not have SELECT * or views. It has limited indexes. In it, the object-relational mapping is automatic and schema changes
are protected
▪ SOQL doesn’t support some of the more advanced features that the SQL SELECT statement does
▪ But on the Salesforce platform, you really don’t need all those extra features
▪ SOQL offers just what you need in a way that makes you feel right at home
Copyright Intellipaat. All rights reserved.
Let us understand DML statements
in Salesforce
Copyright Intellipaat. All rights reserved.
DML Statements
DML statements are the actions which are used to perform insert, update, delete, upsert, restore records, merge records,
or convert leads operations.
Insert Statement Update Statement Upsert Statement Delete Statement
Undelete Statement Merge Statement
Copyright Intellipaat. All rights reserved.
Insert Statement
The insert DML operation adds one or more sObjects, such as individual accounts or contacts, to your organization’s data. Insert is analogous to
the INSERT statement in SQL
Syntax:
Account newAcct = new Account(name = 'Acme');
try {
insert sObject insert newAcct;
insert sObject[] } catch (DmlException e) {
// Process exception here
}
The above example inserts an account named 'Acme'
Copyright Intellipaat. All rights reserved.
Update Statement
The update DML operation modifies one or more existing sObject records, such as individual accounts or contacts invoice statements, in your
organization’s data. Update is analogous to the UPDATE statement in SQL
Account a = new Account(Name='Acme2');
Syntax: insert(a);
Account myAcct = [SELECT Id, Name, BillingCity FROM Account WHERE Id = :[Link]];
[Link] = 'San Francisco';
update sObject
update sObject[]
try {
update myAcct;
} catch (DmlException e) {
// Process exception here
}
The above example updates the BillingCity field on a single account named 'Acme'
Copyright Intellipaat. All rights reserved.
Upsert Statement
The upsert DML operation creates new records and updates sObject records within a single statement, using a specified field to determine the
presence of existing objects or the ID field if no field is specified
Syntax:
List<Account> acctList = new List<Account>();
// Fill the accounts list with some accounts
upsert sObject [opt_field]
try {
upsert sObject[] [opt_field]
upsert acctList;
} catch (DmlException e) {
}
This example performs an upsert of a list of accounts
Copyright Intellipaat. All rights reserved.
Delete Statement
The delete DML operation deletes one or more existing sObject records, such as individual accounts or contacts, from your organization’s data.
Delete is analogous to the delete() statement in the SOAP API
Syntax:
Account[] doomedAccts = [SELECT Id, Name FROM Account
WHERE Name = 'DotCom'];
try {
delete sObject
delete doomedAccts;
delete sObject[]
} catch (DmlException e) {
// Process exception here
}
The above example deletes all accounts that are named 'DotCom'
Copyright Intellipaat. All rights reserved.
Undelete Statement
The undelete DML operation restores one or more existing sObject records, such as individual accounts or contacts, from your organization’s
Recycle Bin. Undelete is analogous to the UNDELETE statement in SQL
Syntax:
Account[] savedAccts = [SELECT Id, Name FROM Account WHERE
Name = 'Universal Containers' ALL ROWS];
try {
undelete sObject | ID
undelete savedAccts;
undelete sObject[] | ID[]
} catch (DmlException e) {
// Process exception here
}
The above example undeletes an account named 'Universal Containers’
Copyright Intellipaat. All rights reserved.
Merge Statement
The merge statement merges up to three records of the same sObject type into one of the records, deleting the others, and re-parenting any
related records
List<Account> ls = new List<Account>{new Account(name='Acme Inc.'),new
Account(name='Acme')};
Syntax: insert ls;
Account masterAcct = [SELECT Id, Name FROM Account WHERE Name = 'Acme
Inc.' LIMIT 1];
merge sObject Account mergeAcct = [SELECT Id, Name FROM Account WHERE Name = 'Acme'
merge sObject sObject[] LIMIT 1];
merge sObject ID try {
merge sObject ID[] merge masterAcct mergeAcct;
} catch (DmlException e) {
// Process exception here
}
The above example merges two accounts named 'Acme Inc.' and 'Acme' into a single record
Copyright Intellipaat. All rights reserved.
SaveResult Class
The result of an insert or update DML operation returned by a Database method.
• An array of SaveResult objects is returned with the insert and update database methods
• Each element in the SaveResult array corresponds to the sObject array passed as the sObject[] parameter in the Database method, that is, the first element in the SaveResult
array matches the first element passed in the sObject array, the second element corresponds with the second element, and so on
• The following are methods for SaveResult:
o getErrors(): If an error occurred, returns an array of one or more database error objects providing the error code and description. If no error occurred, returns an
empty set
o getId(): Returns the ID of the sObject you were trying to insert or update
o isSuccess(): Returns a Boolean that is set to true if the DML operation was successful for this object, false otherwise
Copyright Intellipaat. All rights reserved.
SaveResult Class: Example
// Create two accounts, one of which is missing a required field
Account[] accts = new List<Account>{
new Account(Name='Account1'),
new Account()}; • The following example shows how to obtain and iterate through
[Link][] srList = [Link](accts, false);
the returned [Link] objects
// Iterate through each returned result • It inserts two accounts using [Link] with a false second
for ([Link] sr : srList) {
if ([Link]()) { parameter to allow partial processing of records on failure
// Operation was successful, so get the ID of the record that • One of the accounts is missing the Name required field, which
was processed
[Link]('Successfully inserted account. Account ID: ' + causes a failure
[Link]()); • Next, it iterates through the results to determine whether the
}
else { operation was successful or not for each record
// Operation failed, so get all errors • It writes the ID of every record that was processed successfully
for([Link] err : [Link]()) {
[Link]('The following error has occurred.'); to the debug log, or error messages and fields of the failed
[Link]([Link]() + ': ' + records
[Link]());
[Link]('Account fields that affected this error: ' + • This example generates one successful operation and one
[Link]()); failure
}
}
}
Copyright Intellipaat. All rights reserved.
Transaction Control
▪ SavePoint and Rollback will help us to maintain transaction for DML statement.
▪ Suppose you have written multiple lines of DML statements in a try block, If any error occurs during DML Operations, the operation will be rolled back to the most recent save
point and the entire transaction will not be aborted.
Example:
Savepoint sp;
try{
sp = [Link]();
Account a = new Account();
[Link] = 'Test Account';
insert a; In this example, if any error occurs while inserting the Account ‘a’ or
Contact c = new Contact(Account = [Link]); Contact ‘c’, then the entire transaction will be rolled back to SavePoint ‘sp’,
[Link] = 'Biswajeet';
[Link] = 'Samal'; as specified in the catch section by [Link] method
insert c;
}
catch(Exception e){
[Link](sp);
}
Copyright Intellipaat. All rights reserved.
SOQL Limits
▪ There are certain limits on SOQL which you must be aware of:
Feature Limit Limit Description
SOQL statements Maximum length of SOQL statements By default, 20,000 characters.
SOQL WHEREclause Maximum length of SOQL WHERE clause 4,000 characters.
Copyright Intellipaat. All rights reserved.
DMLOptions Class
Enables you to set options related to DML operations.
• [Link] is only available for Apex saved against API versions 15.0 and highe
• DMLOptions settings take effect only for record operations performed using Apex DML and not through the Salesforce user interface
• The DMLOptions class has three child options:
o [Link]: Enables setting assignment rule options
o [Link]: Determines options for using duplicate rules to detect duplicate records. Duplicate rules are part of the Duplicate Management feature
o [Link]: Enables setting email options
Copyright Intellipaat. All rights reserved.
DmlOptions Properties
Property Description
allowFieldTruncation Specifies the truncation behavior of large strings
assignmentRuleHeader Specifies the assignment rule to be used when creating a case or lead
emailHeader Specifies additional information regarding the automatic email that gets sent when an events occurs
localeOptions Specifies the language of any labels that are returned by Apex
optAllOrNone Specifies whether the operation allows for partial success
Copyright Intellipaat. All rights reserved.
A Demo on DML
Statements
Copyright Intellipaat. All rights reserved.
Quiz
SOQL and SOSL are same.
A True
B False
Copyright Intellipaat. All rights reserved.
Answer
SOQL and SOSL are same.
A True
B False
Copyright Intellipaat. All rights reserved.
Quiz
DML statements and SOQL provide same functionalities.
A True
B False
Copyright Intellipaat. All rights reserved.
Answer
DML statements and SOQL provide same functionalities.
A True
B False
Copyright Intellipaat. All rights reserved.
Quiz
In SOQL select statement we can have maximum 40K
characters
A True
B False
Copyright Intellipaat. All rights reserved.
Quiz
In SOQL select statement we can have maximum 40K
characters
A True
B False
Copyright Intellipaat. All rights reserved.
India: +91-7847955955
US: 1-800-216-8930 (TOLL FREE)
sales@[Link]
24/7 Chat with Our Course Advisor
Copyright Intellipaat. All rights reserved.