DML Operation contd
This command incorporates two DML operation i.e. insert and update in one statement. It has to be provided with a unique identifier such as Record Id, so that it can search the database and find the requested record, if there is no record matching the record Id then a new record is created, if a match found then the record is updated. But if the record Id or key passed have multiple matches, then it neither updates nor creates a record and throw an error
Upsert operation is supported in Dataloader, Apex, and API. It is feasible to create parent child relationship in one statement.
Below is the schematic representation of an Upsert Statement.
Benefits of using Upsert:
It simplifies the process of insertion and updating the records, information about Salesforce Id is not mandatory we can use external Id for this purpose. We do not have to manually export and match the records. It helps in preventing the duplication of records.
Best Practice for Upsert:
Make external Id, Unique and case insensitive. Make use of compound fields to make a unique identifiers.
Use Case for Upsert:
Export data from production to sandbox.
- Create a new field and make it as external Id, follow the best practice to make this field
- Deploy new field in the sandbox
- Start export of data from production and import to sandbox using the various tools available like dataloader or data import wizard
- Use the newly created field for matching the data in sandbox.
Upsert command and external id are very handy when we are integrating data from external datasource, with the help of external id we can reference our record. Most of the data uploading tool like dataloader, dataimport wizard or 3rd party connectore like boomi has options to specify the external Id.
If there are duplicates in your org then Merge operation comes into picture to help you clean up the data.
Merge operation can take up to three records , update the selected record and delete the other two records, in case the delete records have related objects then these objects get reparented.
Merge is mainly available on Contacts, lead and account object
Steps to merge these standard objects are mentioned below: