Perform Bulk Delete

In CCOM v1.4, you can delete high volumes of data using the bulk delete feature. The bulk delete feature is implemented using the Bulk Delete API that deletes the top-level object records synchronously and the child object records asynchronously through a utility. The utility deletes the child object records according to a preconfigured schedule.

This topic contains the following sections:

• Supported Objects for Bulk Delete
• Architecture of Bulk Delete
• Regular Delete Vs. Bulk Delete
• Perform Bulk Delete
• Limitations of DELETE ROQL queries
• Examples of DELETE ROQL queries
• Permissions for Bulk Delete
• Best Practices for Bulk Delete

Supported Objects for Bulk Delete

The bulk delete feature is supported on the following objects:

• Accounts
• Contacts
• Incidents
• Organizations
• Opportunities
• Custom Objects

Architecture of Bulk Delete

The bulk delete feature is comprised of the following components:

• Bulk Delete API
  • It's a public API available for direct customer usage.
  • It deletes top-level object records synchronously and delegates the handling of orphaned child object records to an asynchronous process.
  • The API response indicates the number of records deleted.
• Asynchronous utility that deletes the child object records asynchronously.

Regular Delete Vs. Bulk Delete

There are specific differences between the regular delete and bulk delete outputs in certain cases as listed below:

• Deleting Contacts:
  • Deleting a contact using regular delete, cascade deletes the incidents associated with the contact if it is the last contact associated with those incidents.
  • Deleting contacts using bulk delete, does not delete the incidents associated with those contacts.
• Deleting Organizations:
  • Deleting an organization using regular delete, cascade deletes the contacts and incidents associated with that organization.
  • Deleting organizations using bulk delete, does not delete the contacts and incidents associated with those organizations.

Perform Bulk Delete

You can perform bulk deletes by executing DELETE ROQL tabular queries on the queryResults resource. For bulk delete, all deletions happen on the operational database only.

Note:

An error occurs if you use use report database for bulk delete.

The syntax for the bulk delete API call on the queryResults resource is as follows:

https://your_site_interface/services/rest/connect/version/queryResults/?query=semicolon-separated ROQL DELETE queries

The syntax of ROQL DELETE query is as follows:

DELETE FROM primary object WHERE where clause expression ORDER BY fields LIMIT number of rows

• The ROQL DELETE query supports the WHERE, ORDER BY, and LIMIT clauses.
• primary object is the resource whose records you want to delete, for example, Incidents, Opportunities, and so on.
• where clause expression is the filter condition for the query.
• fields are resource attribute names by which you want to sort the result set in an ascending or descending order. For descending order, the value is desc and for ascending order the value is asc.

Limitations of DELETE ROQL queries

The limitations of the DELETE ROQL query are as follows:

• The maximum number of records for each query is 1000.
  • An error occurs if a query fetches more than 1000 records.
  • Best practice is to use LIMIT 1000 at the end of each query.
• The total number of records that you can delete for each API call is 20,000.

Examples of DELETE ROQL queries

The following are some examples of DELETE queries:

• DELETE FROM Incidents LIMIT 1000; DELETE FROM Incidents LIMIT 1000; DELETE FROM Incidents LIMIT 1000; DELETE FROM Incidents LIMIT 1000;...
• DELETE FROM Incidents WHERE ID >= 1 AND ID < 1000; DELETE FROM Incidents WHERE ID >= 1000 AND ID < 2000; DELETE FROM Incidents WHERE ID >= 2000 AND ID < 3000; DELETE FROM Incidents WHERE ID >= 3000 AND ID < 4000; DELETE FROM Incidents WHERE ID >= 99000 and ID <100001;
• DELETE FROM Opportunities WHERE ID < 150000 LIMIT 1000; DELETE FROM Opportunities WHERE ID < 150000 LIMIT 1000; DELETE FROM Opportunities WHERE ID < 150000 LIMIT 1000;...
• DELETE from Contacts WHERE CreatedTime < '2014-01-01T00:00:00Z' LIMIT 1000; DELETE from Contacts WHERE CreatedTime < '2014-01-01T00:00:00Z' LIMIT 1000; DELETE from Contacts WHERE CreatedTime < '2014-01-01T00:00:00Z' LIMIT 1000;...
• DELETE FROM CO.MyObject where CreatedByAccount= 12 LIMIT 1000; DELETE FROM CO.MyObject where CreatedByAccount= 12 LIMIT 1000; DELETE FROM CO.MyObject where CreatedByAccount= 12 LIMIT 1000;...

  Note:

  MyObject is a custom object.

Note:

For complex queries using GROUP BY/HAVING, perform a SELECT to fetch the IDs and pass the IDs as input in the DELETE statement as follows:

DELETE from primary object where ID in (comma separated list of IDs);

Permissions for Bulk Delete

Bulk Delete API can be accessed only by profiles having the required permission. To enable the required permission, in the Profile Permission - Edit page > Administration tab, select the Bulk Delete check box. You get an invalid access error if you try performing bulk delete without having the required permission.

Note:

• Any user who has bulk delete permission can perform the bulk delete operation. The permission check is performed only at the time of deletion of the top-level object records through Bulk Delete API. No child object level permission check is performed during the asynchronous deletion of child objects. For example, if Incidents are bulk deleted, then the Threads child records are asynchronously deleted without checking for the delete permission again.
• No permission check is done at the object level. For example, a user having bulk delete permission can delete Incidents via Bulk Delete API even though Incident delete permission isn't available on the user's profile.

Best Practices for Bulk Delete

Ensure that you do the following:

• Sanitize the inputs passed in the ROQL queries.
• Be careful while assigning permissions for performing bulk delete.