DELETE queries

SQL 4 CDS translates DELETE queries to a set of standard delete requests that are processed by Dataverse / Dynamics 365. It does not perform any unsupported modifications directly to the underlying SQL database.

The supported syntax is the same as SQL Server with the exception that the OUTPUT clause is not supported.

If you are deleting multiple records in the same way it is much more efficient to do so in a single query rather than have multiple queries each deleting a single record by ID - this allows SQL 4 CDS to take advantage of the parallelism featres to delete your records quicker.

If the entire query to find the records to delete can be transformed to a single FetchXML query, SQL 4 CDS can optionally start a Dataverse / Dynamics 365 bulk delete job rather than issuing individual delete requests. You can control this behaviour in the settings. You may find it takes longer overall for the records to be deleted when using a bulk delete job, but has the advantage of running in the background so you can continue with your work.

Examples

Simple record delete

DELETE account
WHERE  accountid = 'D2F952E0-2458-4723-AA26-C42EA1BE5553'

The record with the given ID (if it exists) will be deleted.

Bulk delete

DELETE account
WHERE  industrycode = 1
       AND statecode = 0

All active records with the old industry code will be deleted.

Delete with joins

DELETE account
FROM   account
       INNER JOIN
       contact
       ON account.accountid = contact.parentcustomerid
WHERE  contact.fullname = 'Mark Carrington'

All accounts that have a matching contact will be deleted. Even if an account has two matching contacts, each account record will only be deleted once.