Chapter 5: Data Manipulation with SOQL

In the previous chapters, you’ve honed your skills in querying data using SOQL. Now, it’s time to shift our focus to data manipulation—the art of creating, updating, and deleting records within Salesforce. In this chapter, we’ll explore various data manipulation operations in SOQL, including inserting, updating, deleting records, performing upsert operations, managing transactions, and understanding the impact on data integrity.

Inserting Records

Inserting records is a fundamental aspect of data manipulation. Whether you’re adding new leads, contacts, accounts, or custom objects, SOQL provides a powerful mechanism to insert records. Let’s start with a simple example: inserting a new contact.

Contact newContact = new Contact(FirstName='John', LastName='Doe', Email='johndoe@example.com');
insert newContact;

In this example:

1. We create a new Contact object named newContact.
2. We set the field values for the new contact using named parameters.
3. We use the insert statement to add the new contact to the database.

Updating Records

Updating records allows you to modify existing data in your Salesforce org. Whether you’re changing a contact’s email address or updating an opportunity’s stage, SOQL makes it straightforward to perform updates. Let’s modify the email address of an existing contact:

Contact existingContact = [SELECT Id, FirstName, LastName, Email FROM Contact WHERE FirstName = 'John' AND LastName = 'Doe' LIMIT 1];
if (existingContact != null) {
    existingContact.Email = 'newemail@example.com';
    update existingContact;
}

In this example:

1. We retrieve an existing contact using a SOQL query.
2. We modify the contact’s email address.
3. We use the update statement to save the changes to the database.

Deleting Records

Deleting records involves removing data from your Salesforce org. Use caution when deleting records, as this action is irreversible. Let’s delete a specific contact:

Contact contactToDelete = [SELECT Id FROM Contact WHERE FirstName = 'John' AND LastName = 'Doe' LIMIT 1];
if (contactToDelete != null) {
    delete contactToDelete;
}

In this example:

1. We retrieve the contact we want to delete using a SOQL query.
2. We use the delete statement to remove the contact from the database.

Upsert Operation with External IDs

The upsert operation combines insert and update operations into a single action. It adds new records if they don’t exist or updates existing records if they do, based on a specified external ID field. This is useful for data integration scenarios. Let’s upsert contacts using an external ID field named External_Id__c:

List<Contact> contactsToUpsert = new List<Contact>{
    new Contact(External_Id__c='123', FirstName='Alice', LastName='Smith'),
    new Contact(External_Id__c='456', FirstName='Bob', LastName='Johnson')
};
Database.upsert(contactsToUpsert, Contact.Fields.External_Id__c);

In this example:

1. We create a list of contacts to upsert.
2. We use the Database.upsert method to perform the upsert operation based on the External_Id__c field.

Transaction Control (Rollback and Savepoint)

Transactions in SOQL involve a sequence of operations that are treated as a single unit of work. Transaction control mechanisms, such as rollback and savepoint, ensure data integrity and consistency, even in the face of errors. Let’s explore how to use these mechanisms with an example:

Savepoint sp = Database.setSavepoint();

try {
    // Perform data manipulation operations here

    // If an error occurs, use rollback to undo changes
    if (errorCondition) {
        Database.rollback(sp);
    }

    // If all operations are successful, use commit to save changes
    Database.commit(sp);
} catch (Exception e) {
    // Handle the exception
}

In this example:

1. We use Database.setSavepoint() to create a savepoint at the beginning of the transaction.
2. Within a try block, we perform data manipulation operations.
3. If an error occurs, we use Database.rollback(sp) to undo changes made since the savepoint.
4. If all operations are successful, we use Database.commit(sp) to save changes.

Conclusion

Congratulations! You’ve delved into the realm of data manipulation with SOQL. In this chapter, you’ve learned how to insert new records, update existing ones, and delete data. You’ve also explored the powerful upsert operation with external IDs and mastered transaction control mechanisms for maintaining data integrity.

Data manipulation is a crucial skill for anyone working with Salesforce data. By understanding how to add, modify, and delete records, you can keep your Salesforce org accurate and up-to-date. Additionally, the ability to perform upsert operations and manage transactions ensures that your data remains consistent and reliable, even in the face of errors.

As we move forward in this guide, you’ll continue to enhance your SOQL expertise. In the next chapter, we’ll explore advanced SOQL techniques, including polymorphic queries, dynamic SOQL, advanced relationship queries, and utilizing the Salesforce Object Search Language (SOSL). Get ready to take your SOQL skills to even greater heights and unlock new dimensions of data manipulation and analysis!