.delete table records - soft delete command

  • Article

To soft delete individual records without a system guarantee that the storage artifacts containing these records are deleted as well, use the following command. This command marks records as deleted but doesn't necessarily delete the data from storage artifacts. For more information, see Soft delete.

To delete individual records with a system guarantee that the storage artifacts containing these records are deleted as well, see Data purge.

Syntax

.delete [async] table TableName records [with ( propertyName = propertyValue [, ...])] <| Predicate

Learn more about syntax conventions.

Parameters

Name Type Required Description
async string If specified, indicates that the command runs in asynchronous mode.
TableName string ✔️ The name of the table from which to delete records.
propertyName, propertyValue string A comma-separated list of key-value property pairs. See supported properties.
Predicate string ✔️ The predicate that returns records to delete, which is specified as a query. See note.

Note

The following restrictions apply to the Predicate:

  • The predicate should have at least one where operator.
  • The predicate can only use the following operators: extend, where and project.
  • The predicate can't use externaldata.

Supported properties

Name Type Description
whatif bool If true, returns the number of records that will be deleted in every shard, without actually deleting any records. The default is false.

Returns

The output of the command contains information about which extents were replaced.

Example: delete records of a given user

To delete all the records that contain data of a given user:

.delete table MyTable records <| MyTable | where UserId == 'X'

Example: check how many records would be deleted from a table

To determine the number of records that would be deleted by the operation without actually deleting them, check the value in the RecordsMatchPredicate column when running the command in whatif mode:

.delete table MyTable records with (whatif=true) <| MyTable | where UserId == 'X'

.delete materialized-view records - soft delete command

When soft delete is executed on materialized views, the same concepts and limitations apply.

Syntax - materialized views

.delete [async] materialized-view MaterializedViewName records [with ( propertyName = propertyValue [, ...])] <| Predicate

Learn more about syntax conventions.

Parameters - materialized views

Name Type Required Description
async string If specified, indicates that the command runs in asynchronous mode.
MaterializedViewName string ✔️ The name of the materialized view from which to delete records.
propertyName, propertyValue string A comma-separated list of key-value property pairs. See supported properties.
Predicate string ✔️ The predicate that returns records to delete. Specified as a query.

Note

The same restrictions on the Predicate mentioned for table apply here as well. Soft delete might fail in case of conflicts with the materialization process running in the background. Retrying the operation can help in this case. To avoid conflicts, you can disable the materialized view before executing soft delete, and re-enable it when the operation completes. Usage of function materialized_view() is not allowed in Predicate.

Supported properties - materialized views

Name Type Description
whatif bool If true, returns the number of records that will be deleted in every shard, without actually deleting any records. The default is false.

Example - materialized views

To delete all the materialized view records that contain data of a given user:

.delete materialized-view MyMaterializedView records <| MyMaterializedView | where UserId == 'X'

Example: check how many records would be deleted from a materialized view

To determine the number of records that would be deleted by the operation without actually deleting them, check the value in the RecordsMatchPredicate column while running the command in whatif mode:

.delete materialized-view MyMaterializedView records with (whatif=true) <| MyMaterializedView | where UserId == 'X'

Related content